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 0:00 return a value based on that index number. 0:03 We use the CHOOSE function regularly for 0:06 applying different scenarios to our forecasting tools. 0:08 In this example we're looking at a very simple business, 0:11 we have sales, costs, then profit or loss. 0:14 We can use the CHOOSE function to determine how different amounts of sales 0:20 will impact our profit or loss. 0:23 So here, we've entered CHOOSE, then we have the index number, 0:25 B13, in this case B13, then all the different indexes. 0:31 So, let's retype this formula so we can learn it together. 0:36 CHOOSE index, 0:41 in this case cell B13 then a comma and then all the different choices. 0:46 In this case we want different scenarios for the amount of sales we get, so 0:53 I'm gonna select E13 as the first choice. 0:56 Then choice two, then choice three, so forth and so 1:01 on, until I've selected all the different choices. 1:04 I'm gonna close the parentheticals, and hit Enter. 1:09 So, because the index number here is 5, 1, 1:13 2, 3, 4, 5, the formula returns 50,000. 1:18 If I chose 1 as the index number, it would return the first choice, or $10,000.00. 1:24 Let's see what happens when we apply different cost scenarios too. 1:30 With $10,000.00 in sales and 1:34 $45,000.00 in costs, we lose $35,000.00. 1:39 What kind of cost will we need to turn a profit in the $10,000 sales scenario? 1:44 So I'm going to make this cost, Now based on the index number as well. 1:51 I'm selecting the different choices. 2:02 And you can see that in this hypothetical example when we sell $10,000 and 2:08 have $10,000 in costs, then we have no profit. 2:12 But what if I wanna play around with these numbers so I can show a profit? 2:16 Well, 5,000, 6,000, I can just change the index number and 2:21 it'll return different scenarios and 2:27 automatically update the cells that are referencing them. 2:31
You need to sign up for Treehouse in order to download course files.Sign up