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.
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.
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.
{=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 ({ })