Microsoft Office Tutorials and References

In Depth Information

**Summarizing Loan Options Using a Data Table**

Figure 13-9 shows a two-way data table (in B7:J16) that displays a calculation (payment amount)

for a loan, using eight interest rates and nine loan amounts.

Figure 13-9:
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 reference to cell B5, which contains the

payment calculation: B7=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.