Excel 2010 Bible

Chapter 15: Creating Formulas for Financial Applications

339

FIGURE 15.6

Using a one-way data table to display three loan calculations for various interest rates.

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

1. Enter the formulas that return the results for use in the data table. In this example,
   the formulas are in B6:B8.


2. Enter various values for a single input cell in successive columns. In this example,
   the input value is interest rate, and the values for various interest rates appear in C10:I10.


3. Create a reference to the formula cells in the column to the left of the input values.
   In this example, the range B11:B13 contains simple formulas that reference other cells.
   For example, cell B11 contains the following formula:
   =B6


4. Select the rectangular range that contains the entries from the previous steps. In this
   example, select B10:I13.


5. Choose Data ➪ Data Tools ➪ What-If Analysis ➪ Data Table. Excel displays the Data
   Table dialog box, shown in Figure 15.7.

FIGURE 15.7

The Data Table dialog box.

6. For the Row input cell field, specify the cell reference that corresponds to the vari-
   able in your Data Table column header row. In this example, the Row input cell is B2.


7. Leave the Column input cell field empty. The Column input field is used for two-way
   data tables, described in the next section.