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

result.