Cookies in Use Read more: Cookie Button:Get a free "Cookies in Use" button for your website | Attacat of Edinburgh http://www.attacat.co.uk/resources/cookie-button-generator#ixzz2fW6KXm1C

MAKING EXCEL WORK FOR YOU

Heading

 © Roy Cox 2005 to 2013

Our Privacy & Cookie Policy


Using the CHOOSE Function in Excel

Choose_Function.zip

The CHOOSE Function can be used to select one of several options. The CHOOSE function's first argument determines which of the following values are returned based on it's position in the list and must be numeric. If the first argument is 1 then the first value is returned, if the first argument is 2 then the second value is returned. The list of values can be text, e,g, names, cell addresses or formulas and set out like this

=Choose (position in list, value1, value2, ... value29 )

the first argument determines the position number in the list of defined values to return. It must be a number between 1 and 29 (255 in newer versions of Excel since Excel 2007),so if the third value in the list is needed, the position is 3. The position argument must be an integer between one and the maximum number of values in the defined list of values. This means that if there are ten choices defined in the CHOOSE formula, the position argument must not be more than ten.

The maximum number of values the CHOOSE Function, or indeed any spreadsheet function, can have is 29, increased to 255 in versions since Excel 2007. So if you want to choose more values than the allowed numbers then you will not be able to use the CHOOSE function. In such a case case you could probably use the OFFSET function or a Lookup table.

In the following example we use the values in the CHOOSE function to be cells. The CHOOSE function's first argument is 1. So the first value, i.e the value in A1 is returned -

In the next example the CHOOSE function's first argument is 2. So the value from the second cell (C1) is returned.

One use of the CHOOSE function could be in scenario analysis. For example, the user could make the CHOOSE function return an optimistic, neutral or pessimistic value for a growth rate.

In the example below the CHOOSE function is being used to choose one of three scenarios. This example is interactive, a list of options being provide by using Excel's Data Validation feature. The cell with a blue background can be changed. The "output" is the cell with the green background.

If the first argument is greater than the number of remaining values then an error is returned. In this example 4 is chosen, but there are only 3 options. If this happens CHOOSE returns a #VALUE error.

The maximum number of values the CHOOSE Function, or indeed any spreadsheet function, can have is 29, increased to 255 in versions since Excel 2007. So if you want to choose more values than the allowed numbers then you will not be able to use the CHOOSE function. In such a case case you could probably use the OFFSET function or a Lookup table.

You might expect CHOOSE to return 82 in the above formula (i.e. the second number in the A1:C1 range), but CHOOSE works a different way, it cannot pick one cell from that range. However, you can use CHOOSE with ranges as illustrated below.I

n the following example the CHOOSE function has four values for four divisions. Since the first argument is 3 the CHOOSE function returns the third value, i.e. SUM(C2:C5). The second following value is A2:A5, so the CHOOSE function returns B2:B5. This range is passed to the SUM function and the SUM function returns the sum of the values in that range.

The above formula can be made a little more dynamic by referring to a cell in which the use can select from a Data Validation list.



A slightly different variation allows the choice to be made simpler. The list is 1.North,2.South, etc. Then use the LEFT function to extract the number to use in the CHOOSE part of the formula

Not only can CHOOSE be used to specify different ranges, you can also use it to have different formulas in the same cell



What the formula does:

The List in A1:A3 is numbered, 1.SUM, etc. To get a numerical value the formula uses the LEFT Function to extract 1,2 or 3, this value is then used as the first argument for the the formula allowing different formulas to be used.

You can download an example workbook containing the formulas and notes.


 an example workbook demonstrating these formulas