Microsoft Office Tutorials and References

In Depth Information

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

3.
On the Data tab, click the What-If Analysis button and choose Data

Table on the drop-down list.

You see the Data Table dialog box (refer to Figure 5-6).

4.
In the Row Input Cell or Column Input Cell text box, enter the address

of the cell where the input value is located.

The input value is the value youâ€™re experimenting with in your

analysis. In the case of the worksheet shown in Figure 5-6, the input value is

located in cell B4, the cell that holds the interest rate.

If the new calculations appear in rows, enter the address of the input

cell in the Row Input Cell text box; if the calculations appear in columns

(refer to Figure 5-6), enter the input cell address in the Column Input

Cell text box.

5.
Click OK.

Excel performs the calculations and fills in the table.

To generate the one-input table, Excel constructs an array formula with

the TABLE function. If you change the cell references in the first row or plug

in different values in the first column, Excel updates the one-input table

automatically.

Book III

Chapter 5

Using a two-input table for analysis

In a two-input table, you can experiment with two input cells rather than

one. Getting back to the example of the loan payment in Figure 5-6, you can

calculate not only how loan payments change as interest rates change but

also how payments change if the life of the loan changes. Figure 5-7 shows a

two-input table for examining monthly loan payments given different

interest rates and two different terms for the loan, 15 years (180 months) and 30

years (360 months).

Follow these steps to create a two-input data table:

1.
Enter one set of substitute values below the formula in the same

column as the formula.

In Figure 5-7, different interest rates are entered in the cell range D5:D15.

2.
Enter the second set of substitute values in the row to the right of the

formula.

In Figure 5-7, 180 and 360 are entered. These numbers represent the

number of months of the life of the loan.

3.
Select the formula and all substitute values.

Do this correctly and you select three columns, including the formula,

the substitute values below it, and the two columns to the right of the

formula. You select a big block of cells (the range D4:F15, in this example).