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

data table.

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.

SUMPRODUCT basics

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!