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 ({ }) indicate an array, which means you can't change or delete individual elements in the results. 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 want to delete the results, you must select the entire array and then delete it.
Skipping data tables when calculating workbooks
Because a data table is an array, Excel treats it as a unit, so a worksheet recalculation means that the entire data table is always recalculated. Such a recalculation is not a big problem for a small data table that has just a few dozen formulas. However, it’s not uncommon to have data tables with hundreds or even thousands of formulas, and these larger data tables can slow down worksheet recalculation.
If you’re working with a large data table, you can reduce the time it takes for Excel to recalculate the workbook if you configure Excel to bypass data tables when it’s running the recalculation. Here are the two methods you can use:
Choose Formulas ⇒ Calculation Options ⇒ Automatic Except for Data Tables.
Choose File ⇒ Options to open the Excel Options dialog box, choose Formulas, select the Automatic Except for Data Tables option, and then click OK.
Now every time you calculate a workbook, Excel bypasses the data tables.
Analyzing Data with Goal Seek
What if you already know the formula result you need and you want to produce that result by tweaking one of the formula’s input values? For example, suppose that you know that you need to have $100,000 saved for your children’s college education. In other words, you want to start an investment now that will be worth $100,000 at some point in the future.
This is called a future value calculation, and it requires three parameters:
The term of the investment
The interest rate you earn on the investment
The amount of money you invest each year
Assume that you need that money 18 years from now and that you can make a 4 percent annual return on your investment. Here’s the question that remains: How much should you invest each year to make your goal?
Sure, you could waste large chunks of your life guessing the answer. Fortunately, you don’t have to, because you can put Excel’s Goal Seek tool to work. Goal Seek works by trying dozens of possibilities — called iterations — that enable it to get closer and closer to a solution. When Goal Seek finds a solution (or finds a solution that’s as close as it can get), it stops and shows you the result.
You must do three things to set up your worksheet for Goal Seek:
Set up one cell as the changing cell, which is the formula input cell value that Goal Seek will manipulate to reach the goal. In the college fund example, the formula cell that holds the annual deposit is the changing cell.
Set up the other input values for the formula and give them proper initial values. In the college fund example, you enter 4 percent for the interest
