Microsoft Office Tutorials and References
In Depth Information
Using the DGET Function
33. Across the top row of the table, arrange a list of values that
should be substituted for the other variable. In this example,
the row contains a list of regions that will eventually be sub-
stituted into cell C18.
44. Select the range for the table. This selection should include the
formula as the upper-left corner cell. It should also include
the column and row of headings.
55. From the Data tab, select What-if Analysis, Data Table. The
Data Table dialog appears, asking for two cells.
66. For the row input cell, enter the cell where the regions should be
substituted. In this case, it is cell C18 in the criteria range.
77. For the Column input cell, enter the cell where the values down
the left column will be substituted. In this case, it is cell B18 in
the criteria range. The complete dialog should look as shown in
The result shown in Figure 12.59 is a crosstab analysis that shows
the DSUM for every combination of product and region. Excel actu-
ally creates a TABLE array function to produce the answers. This is
a live formula: If you change the product names or regions, the cells
inside the table recalculate.