Transcripts for the above video clip:

CHOOSE FUNCTION

The CHOOSE function allows you to refer to particular cells depending on a choice made by the user
The 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