Chapter 31: Performing Spreadsheet What-If Analysis
3131
Using the Data Table command wipes out Excel’s undo stack. Operations that you perform prior to using this com-
mand cannot be undone.
Using this table, you can now see the calculated loan values for varying interest rates.
Notice that the Loan Amt column (column F) doesn’t vary. That’s because the formula in
cell C10 doesn’t depend on the interest rate.If you examine the contents of the cells that Excel entered as a result of this command,
you’ll see that the data is generated with a multicell array formula:
{=TABLE(,C7)}A multicell array formula is a single formula that can produce results in multiple cells (see
Chapter 18, “Understanding and Using Array Formulas”). Because the table uses formulas,
Excel updates the table that you produce if you change the cell references in the first row
or plug in different interest rates in the first column.You can arrange a one-input table vertically (as in this example) or horizontally. If you place the values of the input
cell in a row, you enter the input cell reference in the Row Input Cell field of the Data Table dialog box.
Creating a two-input data table
As the name implies, a two-input data table lets you vary two input cells. You can see the
setup for this type of table in Figure 31.6. Although it looks similar to a one-input table,
the two-input table has one critical difference: it can show the results of only one formula
at a time. With a one-input table, you can place any number of formulas, or references to
formulas, across the top row of the table. In a two-input table, this top row holds the values
for the second input cell. The upper-left cell of the table contains a reference to the single
result formula.Using the mortgage loan worksheet, you could create a two-input data table that shows
the results of a formula (say, monthly payment) for various combinations of two input cells
(such as interest rate and down-payment percent). To see the effects on other formulas, you
simply create multiple data tables—one for each formula cell that you want to summarize.The example in this section uses the worksheet shown in Figure 31.7 to demonstrate a two-
input data table. In this example, a company wants to conduct a direct-mail promotion to
sell its product. The worksheet calculates the net profit from the promotion.