A data table is a tool you can use to calculate how one or two variables change the result of a calculation. Use a data table when you want to see the range of results in a worksheet rather than seeing a single result at a time.
When you need to see how only one variable changes the result of a calculation, you can quickly create a data table. You place the input values—the values you want to test—in either a row or a column, and then tell Excel where to find them.
Creating a data table seems odd at first, but you'll quickly get the hang of it. Try this example of putting together a data table that uses one variable.
First, create a new worksheet and set up the straightforward =FV() formula shown in Figure 11–1 to calculate the future value of an investment that pays 10% interest over 12 months, has a period of 60 months, and takes $600 out of your pay packet every month. Follow these steps:
Figure 11–1. The example calculation for the data table uses the FV() function to calculate the future value of a $600-a-month investment.
So far, so good. Now set up the input values for the data table. Follow these steps:
TIP: It's easy enough to type in the input values for this small data table, but if you want to extend the sequence, try entering 48 in cell A7 and 60 in cell A8, selecting the cells, and then dragging the AutoFill handle to fill in the remaining cells.
Your worksheet should now look like the one in Figure 11–2.
Figure 11–2. Enter the input values for the data table in cells A7:A10.
Now you can enter the formula that uses the data table. For the data table to work, you need to place the formula in the cell where Excel expects to find it:
In this case, we're creating a column-oriented data table, so we need to enter the formula in cell B6. Enter the formula =FV(B1,A5,B3), as shown in Figure 11–3—in other words, the same formula as in cell B4, but using cell A5 instead of cell B2 to supply the number of months.
Figure 11–3. Enter the formula in the cell above and to the right of the input values.
NOTE: When you enter the formula for the data table, Excel usually displays either a value that's clearly wrong or an error value. In this example, the formula cell displays the result $0.00, because cell A5 is blank and so is supplying a zero value for the Months argument. In other formulas, a zero value produces an error value. This isn't a problem, as you'll see in a moment.
Now you're ready to set up the data table. Follow these steps:
Figure 11–4. In the Data Table dialog box, enter the row input cell or the column input cell for the data table you're creating.
NOTE: When you're creating a row-oriented data table, click in the Row Input Cell box in the Data Table dialog box, and then click the cell you used for the input value.
Figure 11–5 shows the data table with the results in place.
Figure 11–5. After you create the data table, the results appear alongside the input values.
When you need to analyze what happens when two pieces of information used in a calculation change, create a data table with two variables. For example, when calculating an investment with the FV() function, you may need to look at not only different numbers of months for the period but also different payments.
To create a two-variable data table, you place one set of input data down a column to the left of the table and the other set of input data across a row above the table. For example, Figure 11–6 shows a two-variable data table that uses the FV() function to calculate the future value of an investment:
Figure 11–6. In a two-variable data table, you enter one set of input values down a column (here, column D) and the other set of input values in a row (here, row 5). The formula goes in the cell where the column and row intersect (here, cell D5).
NOTE: When you no longer need a data table, select the table, and then press Delete or choose Home Edit Clear Clear All.
3.17.174.204