Microsoft Office Tutorials and References

In Depth Information

To create this one-way data table, follow these steps:

1.
In the first row of the data table, enter the formulas that return the results.

The interest rate will vary in the data table, but it doesn't matter which interest rate you use for the calcula-

tions as long as the calculations are correct. In this example, the formulas in E2:G2 contain references to

other formulas in column B.

E2: =B6

F2: =B7

G2: =B8

2.
In the first column of the data table, enter various values for a single input cell.

In this example, the input value is an interest rate, and the values for various interest rates appear in D2:D9.

Note that the first row of the data table (row 2) displays the results for the first input value (in cell D2).

3.
Select the range that contains the entries from the previous steps.

In this example, select D2:G9.

4.
Choose Data
⇒
Data Tools
⇒
What-If Analysis
⇒
Data Table.

Excel displays the Data Table dialog box, as shown in Figure 13-6.

Figure 13-6:
The Data Table dialog box

5.
For the Column Input Cell field, specify the formula cell that corresponds to the input variable.

In this example, the Column Input Cell is B2.

6.
Leave the Row Input Cell field empty. Then click OK.

Excel inserts an array formula that uses the TABLE function with a single argument.

Note that the array formula is not entered into the entire range that you selected in Step 4. The first column and

first row of your selection are not changed.

Creating a two-way data table

A
two-way data table
shows the results of a single calculation for different values of two input cells. Figure

13-7 shows the general layout of a two-way data table.