Section 12.3. Variable Data Tables
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 cells to quickly create a table without needing any formula tweaking. Some Excel fans prefer this approach. However, Excel also gives you the ability to create comparison tables through a dedicated feature that can save a few keystrokes.
12.3.1. Creating a One-Variable Data Table
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 performs the calculation you plan to use.
When you create this formula, make sure that you use a cell reference for the value you want to change, not a literal value. Otherwise, Excel won't be able to change the value to calculate the different possibilities.
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 =FV(B3,B2,0,-B1) . The cell B3 supplies the rate (currently 5 percent). This rate is the piece of information that needs to change, so you should enter B3 in the "Column input cell" text box. (When Excel generates the data table it looks at the first column, gets the new rate value, and substitutes this value into the formula where the reference B3 was previously.)
-
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.
|
12.3.2. Creating a Two-Variable Data Table
It's almost as easy to create a two-variable 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 tells the whole story.
|
Note: For some more sophisticated ways to perform what-if analysis, you can read about scenarios and goal seeking in Chapter 19.
EAN: 2147483647
Pages: 185