7.8 The CHOOSE function

The CHOOSE function is used to choose a value from an ordered list given the position in the list. The CHOOSE function could be used to display the actual day rather than a number that represents the day.

The function is written as:

CHOOSE (index_num,value1,value2,...)

Where:

Index_num = a number between 1 and 29 or a formula which results in a number between 1 and 29.

If Index_num is 1, CHOOSE returns the first value in the list. If index_num is 2, CHOOSE returns value2; the second value in the list, and so on.

The value can be a numerical value, a piece of text, a formula or some other action, a cell reference or a range. So for the day of the week the formula could be written:

CHOOSE (3,Sunday,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday), which would return Tuesday.

Spreadsheets exercise 13

The CHOOSE function

Skills practised
  • New file
  • Entering text
  • Entering functions
  1. Have a go at creating the worksheet below. You will use the CHOOSE function to display the client’s order and calculate the charge for the ice-cream. The aim of this exercise is for the user to enter a number for each element of the ice-cream. The choices will then be used to calculate the cost of the ice-cream.
    P0743
  2. To start with you will use the CHOOSE function to display the customer’s choice of flavour. Click on cell G8 of the CHOOSE worksheet created above.
  3. Type = CHOOSE(
  4. Click on D3 as this is where the Index_Number will be entered.
  5. Type a comma ( ͉ ).
  6. Click on B4 then press ( ͉ ).
  7. Click on B5 then press ( ͉ ) and so on until all the possible choices are entered in the function as shown in the formula bar.
  8. Type a ( ) ) and press <Enter> to set the formula. Caramel should appear in the G8 cell, if 5 is entered in D2.
    P0744
  9. Try entering other numbers in the cell D3 from 1 to 6 – the flavour displayed in G8 will also change.
    Note: If you enter other numbers (e.g. 14), you will get error messages.
  10. In cell G6 enter the CHOOSE function to display the choice of cone.
  11. Repeat in cell G10 to display the type of ice-cream chosen.
  12. Check that cell G4 will display the number of scoops entered as set up on the previous page.

Using the CHOOSE function within a formula

A formula can be created that calculates the cost of the ice-cream chosen. The costs associated with each item are listed to the side of the main worksheet area. The user does not need to know about this area. The formula will use the costs for each item based on choices made by the user.

Spreadsheets exercise 14

Using the CHOOSE function within a formula

Skills practised
  • New file
  • Entering text
  • Entering functions
  1. Enter the following formula in cell G19 to calculate cost depending on what is chosen. The base price of any ice-cream is $4, which is stored in cell M14, then additional costs are added, or if yogurt is used, subtracted:
    =M14+CHOOSE(D9,M2,M3,M4,M5)+CHOOSE(D15,M7,M8)+CHOOSE(D18,M10,M11,M12)
    P0745
  2. Test the formula by entering choices in column D. Is the correct price being calculated in G19?