Microsoft Office Tutorials and References

In Depth Information

**Excel Functions for Your Data Model**

Excel Functions for Your Data Model

As we discussed, the optimal data model for any dashboard separates data, analysis, and

presentation into three distinct layers. Although all three layers are important, the analysis layer is where the

real art comes into play. The fundamental task of the analysis layer is to extract information from the

data layer for use in the staging tables that feed your charts, tables, and other dashboard

components. To do this effectively, you need to use formulas that serve as data delivery mechanisms —

formulas that deliver data to a destination range.

You see, the information you need lives in your data layer (typically, a table containing aggregated

data).
Data delivery
formulas are designed to get that data and deliver it to the analysis layer so it can

be analyzed and shaped. The cool thing is that after you’ve set up your data delivery formulas, your

analysis layer automatically updates each time your data layer is refreshed.

Now, take a look at a few Excel functions that work particularly well in data delivery formulas. As you

go through the examples here, you’ll start to see how these concepts come together.

Understanding lookup tables

In the following sections, you’ll see frequent use of the term lookup table. A
lookup table
is essentially

a range of data that holds information in a structure that can be used to extract the needed data

points. In the context of these examples, you can assume the lookup table will be the data layer.

A lookup table can come in several forms:

➤
One column or row:
You may have a list of manager names in a single column. That list can

be used as a lookup table to find a manager based on his name or his position number

within the column.

➤
Range with multiple data columns:
You may have a table with product numbers and

prices. You can use a list table as a lookup to find a specific price based on its corresponding

product number. In this scenario, you need a formula that performs lookup on the product

number to get the appropriate price.

➤
A position array:
In some cases, you need to look up a value solely based on a particular

position within an array of values. For instance, you may need to find the revenue amount

for the 14th week in a year. If you have every value for each week in the year listed in order,

you can extract the revenue amount for the 14th value in the list.

The VLOOKUP function

The VLOOKUP function finds a specific value in the first column of a lookup table and returns the

corresponding value in a specified table column. The lookup table is arranged vertically. In Figure 11-7,

the table on the top shows sales by month and product number. The table on the bottom translates

those product numbers to actual product names. The VLOOKUP function connects the appropriate

product name to each respective product number.