Chapter 36: Performing Spreadsheet What-If Analysis
749
On the CD
This workbook is available on the companion CD-ROM. The file is named mortgage loan data table.xlsx.
Figure 36.3 shows the setup for the data table area. Row 3 consists of references to the formulas in
the worksheet. For example, cell F3 contains the formula =C10, and cell G3 contains the formula
=C11. Row 2 contains optional descriptive labels, and these are not actually part of the data table.
Column E contains the values of the single input cell (interest rate) that Excel will use in the table.To create the table, select the data table range (in this case, E3:I12) and then choose Data ➪ Data
Tools ➪ What-If Analysis ➪ Data Table. Excel displays the Data Table dialog box, shown in
Figure 36.4.FIGURE 36.3Preparing to create a one-input data table.
FIGURE 36.4The Data Table dialog box.
You must specify the worksheet cell that contains the input value. Because variables for the input
cell appear in the left column in the data table, you place this cell reference in the Column Input
Cell field. Enter C7 or point to the cell in the worksheet. Leave the Row Input Cell field blank.
Click OK, and Excel fills in the table with the calculated results (see Figure 36.5).