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:

CHOOSE(index_num,value1,value2,...)

➤
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

data feeds.