Welcome to our series of applied tips and tricks in Excel for accountants and financial analysts wherever you may be. As a professional modeller, FCA and Excel MVP Liam Bastick, director with SumProduct Pty Ltd, turns his attention to the CHOOSE function.
Do you choose to use CHOOSE? This function uses index_number 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_number). 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_number.
The CHOOSE function employs the following syntax to operate:
CHOOSE(index_number, value1, [value2])
The CHOOSE function has the following arguments:
- index_number: this is required and is used to specify which value argument is to be selected. The argument index_number 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_number is 1, CHOOSE returns value1; if it is 2, CHOOSE returns value2; and so on
- if index_number is less than 1 or greater than the number of the last value in the list, CHOOSE returns the #VALUE! error value
- if index_number 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_number. The arguments can be numbers, cell references, defined names, formulas, functions, or text.
It should be further noted that:
- If index_number 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:
Content seriesView full content series
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:
=CHOOSE(index_num, value1, [value2]…)
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, eg
=CHOOSE(index_number, calculation1, calculation2, calculation3, calculation4)
I disagree. Let me explain. In the example below, I have created a lookup table in cells E10:E13 which I have called Data (to create a range name, simply select the cell(s) and type the name into the Name box, which is the area to the left of the formula bar). The calculations are all visible on the worksheet, rather than hidden away in the formula bar. The index_number 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.
About Liam Bastick
Recognised by Microsoft as one of 104 Most Valuable Professionals (MVPs) in Excel worldwide by Microsoft, Liam has over 30 years’ experience in financial model development/auditing, valuations, M&A, strategy, training and consultancy. He has headed Ernst & Young’s modelling team in Melbourne and was an Assistant Director in their strategic valuations team in London. He was also a senior member of the UK Post Office’s M&A and strategy teams and has worked for / assisted various other Australian modelling companies including BPM, Corality, Navigator Project Finance, PKF and SumProduct.
He has worked in the UK, Australia, Belgium, Denmark, France, Germany, Hong Kong, Indonesia, Malaysia, New Zealand, United States, Switzerland and Vietnam, with many internationally recognised clients, constructing and reviewing strategic, operational and valuation models for many high profile IPOs, LBOs and strategic assignments. Liam is a Fellow of the Institute of Chartered Accountants (ICAEW), a Fellow of the Institute of Chartered Management Accountants (CIMA) and is a professional mathematician.