Excel 2010 Bible

Part V: Analyzing Data with Excel

750

FIGURE 36.5

The result of the one-input data table.

Using this table, you can now see the calculated loan values for varying interest rates. If you exam-

ine 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)}

As I discuss in Chapter 16, an array formula is a single formula that can produce results in multiple

cells. 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.

Note
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. n

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 36.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 36.7 to demonstrate a two-input

data table. In this example, a company wants to conduct a direct-mail promotion to sell its prod-

uct. The worksheet calculates the net profit from the promotion.