Advertisement
Online Test Banks
Score higher
See Online Test Banks
eLearning
Learning anything is easy
Browse Online Courses
Mobile Apps
Learning on the go
Explore Mobile Apps
Dummies Store
Shop for books and more
Start Shopping

How to Create a Two-Variable Data Table in Excel 2007

To create a two-variable data table to perform what-if analysis in Excel 2007, you enter two ranges of possible input values for the same formula: a range of values for the Row Input Cell in the Data Table dialog box across the first row of the table, and a range of values for the Column Input Cell in the dialog box down the first column of the table. You then enter the formula (or a copy of it) in the cell located at the intersection of this row and column of input values.

The steps below for creating a two-variable data table follow a specific example (rather than using generic steps) to help you understand exactly how to use this feature in your worksheets. The following figure shows a Sales Projections worksheet in which two variables are used in calculating the projected profit for the year 2008: a growth rate as a percentage of increase over last year’s sales (in cell B3) and expenses calculated as a percentage of last year’s sales (in cell B4). The formula in cell B5 is =B2+(B2*B3)-(B2*B4).

Sales projection worksheet with a series of possible growth and expense percentages to be plugged i
Sales projection worksheet with a series of possible growth and expense percentages to be plugged into a two-variable data table.

The column of possible growth rates ranging from 1% all the way to 5.5% is entered down column B in the range B8:B17, and a row of possible expenses percentages is entered in the range C7:F7. Follow these steps to complete the two-variable data table for this example:

  1. Copy the original formula entered in cell B5 into cell B7 by selecting cell B7 typing = (equal to) and then clicking cell B5.

    For a two-variable data table, the copy of the original formula must be entered at the intersection of the row and column input values.

  2. Select the cell range B7:F17.

    This cell incorporates the copy of the original formula along with the row of possible expenses and growth rate percentages.

  3. Choose What-If Analysis→Data Table in the Data Tools group on the Data tab.

    Excel opens the Data Table dialog box with the insertion point in the Row Input Cell text box.

  4. Click cell B4 to enter the absolute cell address, $B$4, in the Row Input Cell text box.

  5. Click the Column Input Cell text box and then click cell B3 to enter the absolute cell address, $B$3, in this text box.

  6. Click OK.

    Excel fills the blank cells of the data table with a TABLE formula using B4 as the Row Input Cell and B3 as the Column Input Cell.

    Sales projection worksheet after creating the two-variable data table in the range C8:F17.
    Sales projection worksheet after creating the two-variable data table in the range C8:F17.

The array formula {=TABLE(B4,B3)} that Excel creates for the two-variable data table in this example specifies both a Row Input Cell argument (B4) and a Column Input Cell argument (B3). Because this single array formula is entered into the entire data table range of C8:F17, keep in mind that any editing (in terms of moving or deleting) is restricted to this range.

blog comments powered by Disqus
Advertisement
Advertisement

Inside Dummies.com

Dummies.com Sweepstakes

Win an iPad Mini. Enter to win now!