Microsoft Office Tutorials and References

In Depth Information

Figure 13-8:
Using a two-way data table to display payment amounts for various loan amounts and interest

rates.

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

1.
Enter a formula that returns the results that you want to use in the data table.

In this example, the formula in cell B7 is a simple reference to cell B5, which contains the payment calcula-

tion:

=B5

2.
Enter various values for the first input in successive columns of the first row of the data table.

In this example, the first input value is interest rate, and the values for various interest rates appear in C7:J7.

3.
Enter various values for the second input cell in successive rows of the first column of the data table.

In this example, the second input value is the loan amount, and the values for various loan amounts are in

B8:B16.

4.
Select the range that contains the entries from the preceding steps.

For this example, select B7:J16.

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

Excel displays the Data Table dialog box.

6.
For the Row Input Cell field, specify the cell reference that corresponds to the first input cell.

In this example, the Row Input Cell is B2.

7.
For the Column Input Cell field, specify the cell reference that corresponds to the second input cell.

In this example, the Column Input Cell is B1.

8.
Click OK.

Excel inserts an array formula that uses the TABLE function with two arguments.

After you create the two-way data table, you can change the formula in the upper-left cell of the data table. In

this example, you can change the formula in cell B7 to

=PMT(B2*(B3/12),B4,–B1)*B4–B1

This causes the TABLE function to display total interest rather than payment amounts.

If you find that using data tables slows down the calculation of your workbook, choose

Formulas
⇒
Calculation
⇒
Calculation Options
⇒
Automatic Except for Data Tables. Then,

you can recalculate by pressing F9.

Financial Statements and Ratios