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.
Search JabSto ::

Custom Search