Excel Data Analysis For Dummies. Paul McFedries

to get your data into the worksheet (although a bit later in the book, I talk about ways to get Excel to help with that chore, too), but after you’ve done that, it’s time for Excel to get busy.

      In this chapter, you investigate some built-in Excel tools that will handle most of the data-analysis dirty work. I show you how to build two different types of data tables; give you the details on using the very cool Goal Seek tool; delve into scenarios and how to use them for fun and profit; and take you on a tour of the powerful Solver add-in.

Working with Data Tables

      If you want to study the effect that different input values have on a formula, one solution is to set up the worksheet model and then manually change the formula’s input cells. For example, if you’re calculating a loan payment, you can enter different interest rate values to see what effect changing the value has on the payment.

      The problem with modifying the values of a formula input is that you see only a single result at one time. A better solution is to set up a data table, which is a range that consists of the formula you’re using and multiple input values for that formula. Excel automatically creates a solution to the formula for each different input value.

      

Data tables are an example of what-if analysis, which is perhaps the most basic method for analyzing worksheet data. With what-if analysis, you first calculate a formula D, based on the input from variables A, B, and C. You then say, “What happens to the result if I change the value of variable A?” “What happens if I change B or C?” and so on.

      

Don’t confuse data tables with the Excel tables that I talk about in Chapter 3. Remember that a data table is a special range that Excel uses to calculate multiple solutions to a formula.

      Creating a basic data table

      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.

      Creating a two-input data table

      Rather than vary a single formula input at a time — as in the one-input data table I discuss in the preceding section — Excel also lets you kick things up a notch by enabling you to set up a two-input data table. As you might have guessed, a two-input data table is one that varies two formula inputs at the same time. For example, in a loan payment worksheet, you could set up a two-input data table that varies both the interest rate and the term.

To set up a two-input data table, you must set up two ranges of input cells. One range must appear in a column directly below the formula, and the other range must appear in a row directly to the right of the formula. Here are the steps to follow:

      1 Type the input values:To enter the column values, start the column one cell down and one cell to the left of the cell containing the formula.To enter the row values, start the row one cell up and one cell to the right of the cell containing the formula.Figure 2-4 shows an example.FIGURE 2-4: For a two-input data table, enter one set of values in a column and the other in a row.

      2 Select the range that includes the input values and the formula.In the example shown in Figure 2-4, you'd select the range B7:F15.

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

      4 In the Row Input Cell text box, enter the cell address of the input cell that corresponds to the row values you entered.In the example shown in Figure 2-4, the row values are term inputs, so the input cell is C3 (see Figure 2-5).

      5 In the Column Input Cell text box, enter the cell address of the input cell you want to use for the column values.In the example shown in Figure 2-4, the column values are interest rate inputs, so the input cell is C2 (refer to Figure 2-5).

      6 Click OK.Excel displays the results. Figure 2-6 shows the results of the example two-input data table.

      FIGURE 2-5: Enter the addresses of the input cells.

      FIGURE 2-6: The two-input data table results.

When you run the Data Table command, Excel enters an array formula in the interior of the data table. The 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 Data Table dialog box. The braces ({ })