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
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
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
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
Excel displays the Data Table dialog box.
6. For the Row Input Cell field, specify the cell reference that corresponds to the first input
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
In this example, the Column Input Cell is B1.