Definition and Syntax
• CHOOSE selects a value from a series based on an index number.
How to Use It
• CHOOSE(2,dog,cat,pony) will return cat, the second value in the series. It’s only an interesting formula when the index number is a variable on the worksheet. For example, CHOOSE(escalation,1.02,1.025,1.03) references an escalation cell, where you type in 1, 2, 3, etc. Better still, write CHOOSE(escalation,bestCase,worstCase,LikelyCase) and fill in all four variables on the worksheet.
• CHOOSE is limited to a list of 29 values, but your own ability to structure indexed queries probably stops short of that number anyway.
When to Use It
• The CHOOSE function can help you try out multiple scenarios while storing the variables. Keep a pricing table dynamic by escalating the prices with a set of values. Maintain the initial prices and then build an active price table by multiplying those rates. Draw on the active price table for all calculations, even when the escalation is 1.00 to equal the current prices.
• You can also use CHOOSE to run through different titles if you have more than one publication in the same budget model. Tie it in with a macro to run each title, and use Paste Values to report the results on a rollup sheet. The macro will change the value in the cell on which CHOOSE depends to trudge through all your publications.