Microsoft Office Tutorials and References
In Depth Information
Single-variable tables with more than one formula
To create this table, you can again type seven interest rates in cells B3:B9. Then type the
second set of input values—the loan terms, in months—in a row above and to the right of
the first set, as shown in Figure 18-5.
Figure 18-5 Cell B2 contains the formula for this two-variable table, as shown on the Data
Table4 tab of the Goal Seek.xlsx workbook.
After you type the loan amount in a cell outside the table area (cell I2 in this example),
you can create the table formula. Because this is a two-variable table, you must type the
formula in the cell at the intersection of the row and column that contain the sets of input
values—cell B2, in this example. Although you can include as many formulas as you want
in a single-variable data table, you can include only one output formula in a two-variable
table. The formula for the table in this example is =PMT(A2/12, B1, I2).
You’ll notice immediately that the formula in cell B2 returns an error value because of the
two blank placeholder cells, A2 and B1. As you’ll see, this spurious result does not affect the
performance of the table.
Finally, select the data table—the smallest rectangular block that includes all the input
values and the table formula. In this example, the table range is B2:F9. Click the What-If
Analysis button on the Data tab, click Data Table, and finally specify the (empty) input cells.
Because this is a two-variable table, you must define two input cells. For this example, type
the reference for the first input cell, $B$1 , in the Row Input Cell box, and then type the
reference for the second input cell, $A$2 , in the Column Input Cell box. Figure 18-6 shows the