Microsoft Office Tutorials and References
In Depth Information
Chapter 18: Performing a what-if analysis
In the Data Table dialog box, shown in Figure 18-2, specify the location of the input cell in
the Row Input Cell or Column Input Cell box. The input cell is the placeholder cell referred
to by the table formula—in this example, A2. If the input values are arranged in a row,
type the input cell reference in the Row Input Cell box. If the values in the input range are
arranged in a column, as in our example, use the Column Input Cell box.
Figure 18-2 Use the Data Table dialog box to specify the input cell.
After you click OK, Excel enters the results of the table formula (one result for each input
value) in the available cells of the data table range. In this example, Excel enters six results
in the range C3:C9, as shown in Figure 18-3, with a little formatting we added for easier
reading.
Figure 18-3 The monthly loan payments for each interest rate now appear in the data table, as
shown on the Data Table2 tab of the Goal Seek.xlsx workbook.
When you create this data table, Excel enters the array formula { =TABLE(,A2)} in each
cell in the results range C3:C9. In the sample data table, the formula computes the results
of the PMT function by using each of the interest rates in column B. After you build the
table, you can change the loan amount or any of the interest rate values to see the results
immediately.
Note
The TABLE function is an internal function, meaning that you can’t select it in the Insert
Function dialog box or type it manually.
Search JabSto ::




Custom Search