Microsoft Office Tutorials and References
In Depth Information
Excel Functions for Your Data Model
Take a moment to review the basic syntax of the CHOOSE function:
➤ index_num: Allows you to specify the position number of the chosen value in the list of
values. If the third value in the list is needed, the Index_num is 3. The Index_num argument
must be an integer between one and the maximum number of values in the defined list of
values. That is to say, if there are ten choices defined in the CHOOSE formula, the Index_num
argument can’t be more than ten.
➤ value: Represents a choice in the defined list of choices for that CHOOSE formula. The value
arguments can be hard-coded values, cell references, defined names, formulas, or functions.
Starting in Excel 2007, you can have up to 255 choices listed in your CHOOSE functions. In
Excel 2003, you were limited to 29 value arguments.
Applying CHOOSE formulas to a data model
The CHOOSE function is especially valuable in data models where there are multiple layers of data
that need to be brought together. Figure 11-17 illustrates an example where CHOOSE formulas help
pull data together.
In this example, you have two data tables: one for Revenues and one for Net Income. Each contains
numbers for separate regions. The idea is to create a staging table that pulls data from both tables so
that the data corresponds to a selected region.
To understand what’s going on, focus on the formula in cell F3 shown in Figure 11-17. The formula is
CHOOSE($C$2,F7,F8,F9,F10). The index_num argument is actually a cell reference that looks at the value
in cell C2, which happens to be the number 2. As you can see, cell C2 is actually a VLOOKUP formula that
pulls the appropriate index number for the selected region. The list of defined choices in the CHOOSE
formula is essentially the cell references that make up the revenue values for each region: F7, F8, F9, and F10.
So the formula in cell F3 translates to CHOOSE(2, 27474, 41767, 18911, 10590). The answer is 41,767.
Figure 11-17: The CHOOSE formulas ensure that the appropriate data is synchronously pulled from multiple