The CHOOSE function is used to return a value from a list based on a specified position or index. It is mostly used for Scenario analysis in Financial Modeling. For example, when you have options for various input variables in a model – say worst case, mid case and best case – you can assign numbers to each of the 3 cases and then use the CHOOSE function to automatically select input variables model-wide for your scenario analysis. Don’t worry, we would show you how it works later in this post. Let’s first take a look at the syntax & argument.
Syntax & Argument
The Syntax of the CHOOSE function is as follows:
=CHOOSE(index_num, value1, [value2], …)
- Index_num: This is a required argument that specifies the position of the value to choose. The Index number can range from between 1 and 254.
- Value1: The first value to pick from, it is a required argument, and it can be numbers, text value, cell references, defined names or formula
- Value2 (optional): You can have a perfectly functioning CHOOSE function without providing a value2 (so far your index number is also 1). Similar to the Value 1 argument, the Value 2 argument could be numbers, text value, cell references, defined names or formula
CHOOSE Function Examples
The formulas below use the CHOOSE Function to return the 3rd and 4th values form a list
=CHOOSE(4,”Gold”,”Silver”,”Emerald”,”Diamond”) // returns Diamond
If the values you want to select are in various cells, you would need to select each cell containing values and separate them with a comma. Selecting a range of cells as values (e.g C5:E5) would result in #VALUE Errors.
To correctly select the cells, you should use the formula as seen below:
Using the CHOOSE Function for Scenario Analysis
The CHOOSE function is very useful for scenario analysis. In this section we have an input sheet of a financial model with 3 forecast cases namely Optimistic Case, Base Case and Worst Case. Our task is to change all input variables by selecting from a simple dropdown list.
We have created input assumptions for the 3 cases, and a section to select the case in operation to reflect model wide. on Cell L26. In the y-o-y growth % (row 32) and other rows containing “% of revenue” (rows 38, 44 and 50), we need to apply the CHOOSE function to select input for the case chosen on cell L26.
We would use the formula below to select the relevant input for the case chosen on cell L26
That is it, all done. There are various Input variables we created in this model, and the CHOOSE function helps to make switching between cases super easy with just a click of a drop-down list.
