Microsoft Office Tutorials and References

In Depth Information

**Single-variable tables with more than one formula**

Single-variable tables with more than one formula

When you create a single-variable data table, you can include as many output formulas as

you want. If your input range is in a column, type the second output formula directly to the

right of the first one, the third to the right of the second, and so on. You can use different

formulas for different columns, but they must all use the same input cell.

Suppose youâ€™re thinking about buying a house that would require you to take out a

$180,000 mortgage. You want to know what your monthly payments would be on that

mortgage at each of the interest rates in the input range, and you want to be able to

compare these payments with those for the $200,000 mortgage calculated in Figure 18-3. You

can expand the table in Figure 18-3 to include both formulas.

To add a formula to the data table, type the new formula in cell D2. For this example, we

typed
=PMT(A2/12, 360, D1)
. This formula must also refer to cell A2, the same input cell

as in the first formula. Then type
$180,000
in cell D1, and select the table range B2:D9.

Then click the What-If Analysis button on the Data tab, and click Data Table. Finally, type

the same input cell reference ($A$2) in the Column Input Cell box and click OK. Figure 18-4

shows the result.

Figure 18-4
This data table computes the monthly payments on two different loan amounts at

various interest rates, as shown on the Data Table3 tab of the Goal Seek.xlsx workbook.

Data tables based on two input variables

Suppose you want to build a data table that computes the monthly payment on a $200,000

mortgage, but this time you want to vary not only the interest rate but also the term of the

loan. You want to know what effect changing the interest rate and the term have on your

monthly payment.