12.3. Variable Data Tables
When you create a formula, you see a single result based on the operators, functions, and data that you used. This situation makes sense when you're nailing down last year's profit-loss report, but it's not always as handy when you're making projections for the future . In these cases, it's often helpful to compare several possibilities. One tool you can use is a variable data table .
A variable data table is simply a table that shows multiple results, based on different source data. For example, you might use a variable data table to see how the return of an investment varies based on different interest rates. Because all the results are shown side-by-side, you can quickly compare them.
You could create your own comparison table by hand without too much trouble. In fact, using the power of relative references, you could create one formula and copy it into several
A one-variable data table provides a single column of results. It's called one-variable because there's only one input value that changes. For example, if you want to compare how an investment payoff changes based on interest rates, you can create a one-variable data table that uses the interest rate as the changing variable.
Here's how you create a one-variable data table:
Create a sample formula that
When you create this formula, make sure that you use a
In the example in Figure 12-9, the formula uses the FV( ) financial function to calculate the future value of an investment. The variable is the rate argument, which is stored in cell B3.
Set up the table. To do so, create a column that has each value you want to test.
In this example, you need to create a column with the different interest rate possibilities. The calculated results will appear in the column on the right. It can be a little tricky getting the correct arrangement. You need to make sure the formula you want to modify is just above the column where the results will appear. Figure 12-9 helps you figure out how all this fits together.
Generate the table by selecting the whole table and then choosing Data Table.
The Table dialog box appears.
In the "Column input cell" box, type the address of the cell that has the value you want to change.
This is the tricky part. Excel needs to figure out which part of the formula it's supposed to modify to generate the different results that appear in the table. In this example, the formula is
. The cell B3
Click OK to create the table.
Excel fills in all the different resultsin this example, the value of the investment based on different interest rates. If you actually look at the content of each cell, you'll find that it uses an array formula with the TABLE( ) function to work its magic.
It's almost as easy to create a
data table, where two different values change. In this case, you place one set of changing values in the column on the left. You place the other set of changing values in the row on the top. Figure 12-10