Microsoft Office Tutorials and References

In Depth Information

**Editing tables**

Figure 18-6
This data table calculates monthly payments using various interest rates and terms.

TROUBLESHOOTING

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.

Editing tables

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

range.