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.
Search JabSto ::




Custom Search