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
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
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).