Do You Choose to Use CHOOSE?

Introduction 

As a professional modeller, FCA and Excel MVP Liam Bastick highlights some of the more useful functions for financial modelling / Excel spreadsheeting.  Here’s one you might CHOOSE...

The Choice is Yours

Do you choose to use CHOOSE?  This function uses index_num to return a value from the list of value arguments.  CHOOSE may be used to select one of up to 254 values based on the index number (index_num).  For example, if value1 through value7 are the days of the week, CHOOSE returns one of the days when a number between 1 and 7 is used as index_num.

The CHOOSE function employs the following syntax to operate:

The CHOOSE function has the following arguments:

• index_num: this is required and is used to specify which value argument is to be selected. Index_num must be a number between 1 and 254, or a formula or reference to a cell containing a number between 1 and 254.
  • if index_num is 1, CHOOSE returns value1; if it is 2, CHOOSE returns value2; and so on
  • if index_num is less than 1 or greater than the number of the last value in the list, CHOOSE returns the #VALUE! error value
  • if index_num is a fraction, it is truncated to the lowest integer before being used.
• value1, value2, ...: value 1 is required, but subsequent values are optional.  There may be between 1 and 254 value arguments from which CHOOSE selects a value or an action to perform based on index_num.  The arguments can be numbers, cell references, defined names, formulas, functions, or text.

It should be further noted that:

• If index_num is an array, every value is evaluated when CHOOSE is evaluated
• The value arguments to CHOOSE can be range references as well as single values.

For example, the formula:

evaluates to:

which then returns a value based on the values in the range B1:B10.

The CHOOSE function is evaluated first, returning the reference B1:B10. The SUM function is then evaluated using B1:B10, the result of the CHOOSE function, as its argument.  A similar idea is also expressed by the formula

which will return the result of =SUM(A1:A3).

Certainly it is a function used in modelling, but perhaps it is not used as regularly as some others.  This is useful for non-contiguous references:

Just so that we are clear on jargon: a non-contiguous range (with reference to Excel) means a range that cannot be highlighted with the mouse alone.  In the image above, to highlight the cells coloured you would have to press down the CTRL key as well.

INDEX, LOOKUP, VLOOKUP and HLOOKUP all require contiguous references.  They refer to lists, row vectors, column vectors and / or arrays.  CHOOSE is different:

As explained above ,this function allows references to different calculations, workbook / worksheet references, etc.  Try to use the function appropriately.  For instance, a well-known Excel website proposes the following formula for calculating the US Thanksgiving date.  Assuming cell A1 has the year:

To understand this formula, note that DATE(Year,Month,Day) returns a date and WEEKDAY(Date) returns a number 1 (Sunday) through 7 (Saturday).  But doesn’t this formula look horrible?  It is full of hard code and it contains an unnecessary number of arguments.  The formula could exclude CHOOSE viz.

Now let me be clear here.  I am not saying this is a simple, transparent formula.  Test it.  They both provide the same answer.  CHOOSE – and plenty of additional hard code – has been used unnecessarily.

That’s not to say there isn’t a time and a place for CHOOSE.  It is useful when you need to refer to cells on different worksheets or in other workbooks.  Some argue that it is useful when a calculation needs to be computed using different methods, e.g.

I disagree.  Let me explain.  In the example below, I have created a lookup table in cells E10:E13 which I have called Data (I will explain how to create range names later).  The calculations are all visible on the worksheet, rather than hidden away in the formula bar.  The index_num selection, here referred to as Selection_Number, is input in cell E2.  The result?

It’s identical, but easier to follow

I have taught financial modelling to many gifted analysts over the years and a common mistake made by many is that they build models that are easy to build rather than models that are easy to understand.  The end user is the customer.  It should be simple to use: taking shortcuts invariably only helps the modeller – and even then, more often than not, shortcuts will backfire. 

CHOOSE can lead to opaque models that need to be rebuilt and are often less flexible to use.  You have been warned!