13- 17 May, 2013

# Excel Choose Function

How to use the Excel Choose Function to choose between various alternate answers or actions

• 3:23
• Skill Level:
• What's my skill level

## Excel CHOOSE function

The excel choose function is very effective in replacing the IF functions in certain situations.
In this segment you’ll learn how to activate this function and use the CHOOSE function as a replacement for complex IF functions

In this example were trying to work out the total revenue generated, by multiplying the Units Sold by the Selling Price.
In the Units Sold section we have 3 possible scenarios, and in the Selling Price we also have 3 possible scenarios, and what we’d like to be able to do is to type in the number of the scenario we want for Units Sold, and for the Selling Price and the spreadsheet must automatically calculate the total revenue.

The simplest way to do this is to make use of Excel’s CHOOSE command

• So click in the cell where you want the result to be
• Activate the Function Wizard
• And find the CHOOSE command
• Click ok

In the first box you need to indicate to Excel what cell, will tell it what scenario is being chosen,

• so in this case we are looking at the Units Sold ,
• so we want to be able to change this number here,
• because its going to be a constant reference to this cell, we need to make it absolute,
• Value 1 says, when the index number is 1 where must I pick up the number from, in this case we want it to go to
• cenario 1 Units Sold.
• If the index no is 2, we’d like it to point to this cell
• and if it is 3, we’d like it to point to this cell,
• then we can say ok,
• we can copy it across,
• now what you’ll notice is that through here has been pulled scenario 1’s details,
• if we went up here,
• changed this to a 2
• and if you watch down here as I push enter,
• you’ll see its now gone and chosen the 2nd row to pull through and
• similarly you can do it with the 3rd row

We can do the same for the Selling Price,

• if you click in this cell
• activate the Function Wizard
• and find the CHOOSE command
• the index number in this case is now the Selling Price scenario
• and again we make it absolute value,
• and in this case if the Selling Price scenario is 1, we’d like it to refer to that cell,
• if it is 2, we’d like to refer to that cell
• if it is 3, we want it to refer to that cell,
• when we say ok,
• we can now copy it across, and exactly the same way because the scenario is set as 1, its is pulling through this row, and its coming through here,
• if we change it to 2, you’ll see it now pulls through this row
• and you can do the same with 3, and row 3 will come through
• and you now have a way to being able to choose scenarios based on units sold and selling price to generate your total revenue

## Handy Tips & Tricks

Advanced Excel Course – June 2013 in South Africa Our next advanced excel course is running during June 2013 in Johannesburg. During the week of the 10th June we offer the following options. You can either attend the full 5 … Continue reading

## Creating a Waterfall Chart in Excel the easy way

A very popular chart is the waterfall chart which shows the impact of various items on a total e.g. the various components that explain the move from budgeted profit to actual profit. A common way to build this is using … Continue reading

"Adrian and Team. Recently we had a most successful in-house training session at our company. I would like to extend my thanks and gratitude towards De Wet, who hosted the Excel Advanced course.

– Gill Robbetze Lonmin

## View Entire Video Gallery ››

• 03:14
• Skill Level:
• What's my skill level

• 00:42
• Skill Level:
• What's my skill level

## Waterfall chart using stock charts

• 07:11
• Skill Level:
• What's my skill level

## Absolute and Relative cell references

• 06:34
• Skill Level:
• What's my skill level