Microsoft Office Tutorials and References

In Depth Information

**Excel Functions for Your Data Model**

Figure 11-15:
You can use the SUMPRODUCT function to pull summarized numbers from the data layer into

staging tables.

The Choose function

The CHOOSE function returns a value from a specified list of values based on a specified position

number. For instance, if you enter the formulas CHOOSE(3,”Red”, “Yellow”, “Green”, “Blue”) into a cell,

Excel returns Green because Green is the third item in the list of values. The formula CHOOSE(1,”Red”,

“Yellow”, “Green”, “Blue”) returns Red. Although this may not look useful on the surface, the CHOOSE

function can enhance your data models dramatically.

CHOOSE basics

Figure 11-16 illustrates how CHOOSE formulas can help pinpoint and extract numbers from a range

of cells. Note that instead of using hard-coded values, like Red, Green, and so on, you can use cell

references to list the choices.

Figure 11-16:
The CHOOSE function allows you to find values from a defined set of choices.