Microsoft Office Tutorials and References

In Depth Information

**Performing What-If Analyses with Data Tables**

Figure 5-6:

A one-input

data table.

Follow these steps to create a one-input table:

1.
On your worksheet, enter values that you want to substitute for the

value in the input cell.

To make the input table work, you have to enter the substitute values in

the right location:

•
In a column:
Enter the values in the column starting one cell below

and one cell to the left of the cell where the formula is located. In

Figure 5-6, for example, the formula is in cell E4 and the values are in

the cell range D5:D15.

•
In a row:
Enter the values in the row starting one cell above and one

cell to the right of the cell where the formula is.

2.
Select the block of cells with the formula and substitute values.

Select a rectangle of cells that encompasses the formula cell, the cell

beside it, all the substitute values, and the empty cells where the new

calculations will soon appear.

•
In a column:
Select the formula cell, the cell to its left, all the

substitutevalue cells, and the cells below the formula cell.

•
In a row:
Select the formula cell, the cell above it, the substitute

values in the cells directly to the right, and the now-empty cells

where the new calculations will appear.