Microsoft Office Tutorials and References
In Depth Information
Figure 18-6 This data table calculates monthly payments using various interest rates and terms.
The results in my two-input data table are wrong
Be careful not to reverse the input cells in a two-variable table. If you do, Excel uses the
input values in the wrong place in the table formula, which creates a set of meaningless
results. For example, if you reverse the input cells in the example shown in Figure 18-6,
Excel uses the values in the input range C2:F2 as interest rates and the values in the
input range B3:B9 as terms, resulting in monthly payments in the $20 million range!
To be sure you’re using the correct input cells, look at the formula. In our example,
=PMT(A2/12, B1, I2), A2 appears in the first argument, which is rate . Because the rates
are arranged in a column, A2 is the column input cell.
Although you can edit the input values or formulas in the left column or top row of a table,
you can’t edit the contents of any individual cell in the results range because the data table
is an array. If you make a mistake when you set up a data table, you must select all the
results, press the Delete key, and then recompute the table.
You can copy the table results to a different part of the worksheet. You might want to do
this to save the table’s current results before you change the table formula or variables. In
Figure 18-7, we copied the values from C3:F9 to C11:F17. When you do this, the copied
values are constants, not array formulas. Excel automatically changes the results of the table
from a set of array formulas to their numeric values if you copy the results out of the table