Ms Office Tune Up

Modifying formula variables suffers from a serious drawback: You see only a single result at one time. If you want to study the effect that a variety of values has on the formula, you need to construct a data table. In the loan payment worksheet, for example, suppose that you want to see the payments with the principal varying between $100,000 and $150,000. You can just enter these values into a row or column, and then create the appropriate for­mulas. Setting up a data table, however, is much easier, as the following procedure shows:

1. Add to the worksheet the values you want to input into the formula. You have two choices for the placement of these values:
   • If you want to enter the values in a row, start the row one cell up and one cell to the right of the formula.
   • If you want to enter the values in a column start the column one cell down and one cell to the left of the cell containing the formula, as shown in Figure 1.
2. Select the range that includes the input values and the formula. (In Figure 1, this is B8:C14.)
3. Choose Data, What-If Analysis, Data Table. Excel displays the Data Table dialog box.
4. How you fill in this dialog box depends on how you set up your data table:
   • If you entered the input values in a row, use the Row Input Cell text box to enter the cell address of the input cell.
   • If the input values are in a column, enter the input cell’s address in the Column Input Cell text box. In the future value example, you enter C5 in the Column Input Cell, as shown in Figure 2.


Figure 1 Enter the values you want to input into the formula.


Figure 2 In the Data Table dialog box, enter the input cell in which you want Excel to substitute the input values.

5. Click OK. Excel places each of the input values in the input cell; Excel then displays the results in the data table, as shown in Figure 3.

Figure 3 Excel substitutes each input value into the input cell and displays the results in the data table.

You also can set up data tables that take two input variables. For example, this enables you to see the effect on a loan payment’s value when you enter different values for the principal and the interest rate. The following steps show you how to set up a two-input data table:

1. Enter one set of values in a column below the formula and the second set of values to the right of the formula in the same row, as shown in Figure 4.


Figure 4 Enter the two sets of val­ues that you want to input into the formula.

2. Select the range that includes the input values and the formula (B8:G14 in Figure 4).
3. Choose Data, What-If Analysis, Data Table to display the Data Table dialog box.
4. In the Row Input Cell text box, enter the cell address of the input cell that corre­sponds to the row values you entered (C3 in Figure 4-the Annual Interest variable).
5. In the Column Input Cell text box, enter the cell address of the input cell you want to use for the column values (C5 in Figure 4-the Principal variable).
6. Click OK. Excel runs through the various input combinations and then displays the results in the data table, as shown in Figure 5.

Figure 5 Excel substitutes each input value into the input cell and displays the results in the data table.

TIP

If you make changes to any of the variables in a table formula, Excel recalculates the entire table. This isn’t a problem in small tables, but large ones can take a long time to calculate. If you prefer to control the table recalculation,choose Formulas,Calculation Options,Automatic Except Tables.This tells Excel not to include data tables when it recalculates a worksheet.To recalculate all data tables in a workbook, press F9 (or Shift+F9 to recalculate the current worksheet only).

If you want to make changes to the data table, you can edit the formula (or formulas) as well as the input value. However, the data table results are a different matter. When you run the Data Table command, Excel enters an array formula in the interior of the data table. This formula is a TABLE() function (a special function available only by using the Data Table command) with the following syntax:

{=TABLE(row_input_ref, column_input_ref)}

Here, row_input_ref and column_input_ref are the cell references you entered in the Table dialog box. The braces ({ }) indicate that this is an array, which means that you can’t change or delete individual elements of the array. If you want to change the results, you need to select the entire data table and then run the Data Table command again. If you just want to delete the results, you must first select the entire array and then delete it.

Technorati Tags: multiple solutions, calculating formula

Popularity: 5% [?]