At first glance the CHOOSE function isn’t very exciting and typically you have to team it up with other functions to get the party started. Fair enough I suppose, after all, the more the merrier.
In this tutorial we’re going to use choose to toggle through different aggregation methods (AVERAGE, MAX, MIN, SUM) as seen here:
Download the Workbook
But first a quick rundown for those not familiar with CHOOSE:
The syntax is:
CHOOSE(index_num, value1, [value2], [value3],….)
It returns a value from a list based on the index_num argument.
A simple example:
Would return PivotTables as it’s the second value in the list.
Would return Tables because the index_num argument is 4, and Tables is the 4th value.
One of the unique features of CHOOSE is that the value arguments can be:
- Cell references
- Defined names
- Text (as in the above examples)
That’ a long list of options and provides a huge range of applications (and opportunities to party...sorry, I couldn't help myself ;-)).
There are a few moving parts to this technique:
Is your head in a spin? Let me explain them:
- CHOOSE's index_num argument comes from a Table called agg that maps the aggregation type to the index number:
- Slicers (available in Excel 2010 onwards) provide the interactivity that enables the user to toggle through the different aggregation methods:
- The Slicer filters a mini PivotTable (mini being small and with the Grand Total line removed, as opposed to some special breed of PivotTable):
- Our data is formatted in an Excel Table called ProgramTbl2 (see below) and this means our formula will be using Structured References to reference the table.
- And our CHOOSE formula in cell I9 references cell L13 of the PivotTable to find which aggregation method was selected:
=CHOOSE(L13, AVERAGE(ProgramTbl2[Viewers]), MAX(ProgramTbl2[Viewers]), MIN(ProgramTbl2[Viewers]), SUM(ProgramTbl2[Viewers]),)
Bonus: in cell H9 there is a dynamic text label which also uses CHOOSE to display which aggregation method has been chosen:
Excel 2007 Method
For those of you still using Excel 2007 you don’t have the luxury of Slicers but you can achieve the same results using Form Control Radio Buttons:
- To insert radio buttons you first need to enable the Developer tab in your Ribbon.
- After selecting the Radio Button from the Insert drop down on the Developer tab simply left click and drag the mouse to draw it on your workbook. Right-click it to edit the text.
- Make sure you put them in a Group box (Form Control) so Excel knows they’re all part of a group and numbers them consecutively. Click and drag to draw the Group Box on your worksheet just like you do with the Radio Buttons.
Tip: the whole of each Radio Button must be inside the bounds of the Group Box.
- Set the Cell Link cell for each Radio Button. Right-click the Radio Button > Format Control > Control tab and choose a cell for the Cell Link (mine is in cell E7):
The Cell Link is a cell anywhere in your workbook which captures the number of the selected radio button. All 4 of your radio buttons should use the same cell as this populates the index_num argument in your CHOOSE formula.
Note: you’d normally put your Cell Link cell out of sight. I put beside the radio buttons so you can see all the moving parts together.
Tip: you can copy and paste the first Radio Button and it will remember the cell link so you only have to set the Cell Link once.
- Link your index_num argument to the Cell Link cell.
Uses for this technique
- Headline figures in your Dashboard reports
- A quick way to summarise your data in different ways
- Use it with named ranges to return different regions/product group summaries
More CHOOSE Examples
- A fairly common way to use CHOOSE is to force VLOOKUP to look to the left. Although I prefer INDEX & MATCH for this.
- Mike Alexander shows us a clever way to use CHOOSE to convert a date into a fiscal quarter.
Totals in Excel Tables
Excel Tables also enable you to choose the aggregation method by clicking on the down arrow in the Total cell:
It’s a nice touch, however there are two things I don’t like about this:
- The ‘Total’ label in the first column doesn’t change to tell you what aggregation method is in use. You can choose Standard Deviation and ‘Total’ still says ‘Total’.
- It’s at the bottom of the Table which can be a long way away.
If you liked this or know someone who could use it please click the buttons below to share it with your friends on LinkedIn, Google+, Facebook and Twitter.