Microsoft Office Tutorials and References
In Depth Information
Excel Functions for Your Data Model
Applying HLOOKUP formulas to a data model
HLOOKUPs are especially handy for shaping data into structures appropriate for charting or other
types of reporting. A simple example is demonstrated in Figure 11-11. With HLOOKUPs, the data
shown in the raw data table at the bottom of the figure is reoriented in a staging table at the top.
When the raw data is changed or refreshed, the staging table captures the changes.
Figure 11-11: In this example, HLOOKUP formulas pull and reshape data without disturbing the raw
The SUMPRODUCT function
The SUMPRODUCT function is actually listed under the math and trigonometry category of Excel
functions. Because the primary purpose of SUMPRODUCT is to calculate the sum product, most
people don’t know you can actually use it to look up values. In fact, you can use this versatile function
quite effectively in most data models.
The SUMPRODUCT function is designed to multiply values from two or more ranges of data and then
add the results together to return the sum of the products. Take a look at Figure 11-12 to see a
typical scenario where the SUMPRODUCT is useful.
In Figure 11-12, you see a common analysis where you need the total sales for the years 2012 and
2011. As you can see, to get the total sales for each year, you first have to multiply Price by the
number of Units to get the total for each Region. Then you have to sum those results to get the total sales
for each year.
With the SUMPRODUCT function, you can perform the two-step analysis with just one formula.
Figure 11-13 shows the same analysis with SUMPRODUCT formulas. Instead of using 11 formulas, you
can accomplish the same analysis with just three!