Читать книгу Excel Data Analysis For Dummies - Paul McFedries - Страница 36

The most basic type of data table is one that varies only one of the formula’s input cells. Not even remotely surprisingly, this basic version is known far and wide as a one-input data table. Here are the steps to follow to create a one-input data table:

1 Type the input values.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 2-1.To enter the values in a row, start the row one cell up and one cell to the right of the cell containing the formula.

2 Select the range that includes the input values and the formula.In the example shown in Figure 2-1, you'd select the range B7:C15.FIGURE 2-1: This data table has the input values in a column.

3 Choose Data ⇒ What-If Analysis ⇒ Data Table to open the Data Table dialog box.

4 Enter the address of the input cell, which is the cell referenced by the formula that you want the data table to vary.That is, for whatever cell you specify, the data table will substitute each of its input values into that cell and calculate the formula result. You have two choices:If you entered the input values in a row, enter the input cell’s address in the Row Input Cell text box.If the input values are in a column, enter the input cell’s address in the Column Input Cell text box. In the example shown in Figure 2-1, the data table’s input values are annual interest rates, so the column input cell is C2, as shown in Figure 2-2.

5 Click OK.Excel fills the input table with the results. Figure 2-3 shows the results of the example data table.

When you see the data table results, you might find that all the calculated values are identical. What gives? The problem most likely is Excel’s current calculation mode. Choose Formulas ⇒ Calculation Options ⇒ Automatic, and the data table results should recalculate to the correct values.

FIGURE 2-2: Enter the address of the input cell.

FIGURE 2-3: The data table results.