Bummer! This is just a preview. You need to be signed in with a Basic account to view the entire video.
The CHOOSE Function2:37 with Michael Watson
The CHOOSE function allows you to enter an index number and return a value based on that index. It's used regularly for applying different scenarios to business forecasting.
- you can continue to use the spreadsheet from the last video, or
- open a copy of this spreadsheet to catch up to this video
The CHOOSE function allows you to enter an index number and
return a value based on that index number.
We use the CHOOSE function regularly for
applying different scenarios to our forecasting tools.
In this example we're looking at a very simple business,
we have sales, costs, then profit or loss.
We can use the CHOOSE function to determine how different amounts of sales
will impact our profit or loss.
So here, we've entered CHOOSE, then we have the index number,
B13, in this case B13, then all the different indexes.
So, let's retype this formula so we can learn it together.
in this case cell B13 then a comma and then all the different choices.
In this case we want different scenarios for the amount of sales we get, so
I'm gonna select E13 as the first choice.
Then choice two, then choice three, so forth and so
on, until I've selected all the different choices.
I'm gonna close the parentheticals, and hit Enter.
So, because the index number here is 5, 1,
2, 3, 4, 5, the formula returns 50,000.
If I chose 1 as the index number, it would return the first choice, or $10,000.00.
Let's see what happens when we apply different cost scenarios too.
With $10,000.00 in sales and
$45,000.00 in costs, we lose $35,000.00.
What kind of cost will we need to turn a profit in the $10,000 sales scenario?
So I'm going to make this cost, Now based on the index number as well.
I'm selecting the different choices.
And you can see that in this hypothetical example when we sell $10,000 and
have $10,000 in costs, then we have no profit.
But what if I wanna play around with these numbers so I can show a profit?
Well, 5,000, 6,000, I can just change the index number and
it'll return different scenarios and
automatically update the cells that are referencing them.
You need to sign up for Treehouse in order to download course files.Sign up