Instructor: Let's take a few minutes
and talk about one of Excel's lesser known
lookup reference functions, formula called choose.
Now, the choose function allows you to select a value,
cell reference, or function out of a list
based on a given index number.
So take a look at the syntax here, pretty simple.
The first argument is the index number.
Which item in the following list
inside of the formula itself
should be returned or evaluated?
Next up you have the first item in the list.
It's required.
You have to have a list containing at least one item,
and then from there you have optional arguments
for the second item in the list,
third, fourth, fifth, and so on.
So you may be wondering, "What's the point of this,
it seems kind of silly," but here's some fun facts
about choose that make this function pretty unique.
First of all, those list items
can include virtually anything, not just numbers or text.
They can be cell references,
they can be defined names or ranges,
they can be formulas themselves,
they can be text or any mix of those.
So very, very flexible kind of function.
Second, it actually acts like an indirect function
automatically, which means that it interprets
cell references, like A1:B10, as an actual reference
to cell locations, as opposed to treating that as text
like most functions would.
And then third, you can combine choose with other functions,
or get this, nest it directly into a cell reference itself.
And I'm gonna show you an example
of exactly how you can do that.
So with that, there's your primer on the choose function.
Let's go ahead and shift into Excel mode
and practice putting this to the test.
All right, great.
So go ahead and open up your section five
look up reference workbook if you'd like to follow along.
And I'm in the choose tab right here at the end.
If you don't see that choose tab,
it just means you need to jump back
to the download course resources lesson
back in the course intro, and you should be good to go.
So what we've got here is a pretty simple table of data.
We're looking at IMDB ratings by year in columns,
and by movie genre in rows.
And what we're gonna do here is build ourselves
a little tool where we can select one of the three years,
and even select a type of calculation to perform
and then return the proper ratings
from our little table here.
So let's start with the year cell right here in H4.
And the first thing I'm gonna do is kind of silly,
but it'll help us understand
what the choose function's actually doing.
So I'm gonna type =CHOOSE.
And the first argument, remember, is that index number.
I'm just gonna put a one there and comma over,
and now I just entered the items in my list.
So let's say item one is the year 2016, comma over.
Item 2 is 2017, item 3, 2018, close it off, press enter.
So that returns to 2016 because the first item
in the list is 2016.
If I change that 1 to a 2, I'll see 2017 and so on.
Now, obviously not a very practical example,
because that one is hard-coded.
In fact, it's a two now.
So not really a user-friendly way
to deploy the choose function in this case.
If we want someone to be able to choose
any of these three years, a more efficient way to do it
would be to use something like a data validation list.
So let's delete that formula.
Let's go into data, data validation,
and our data tool section, we're gonna allow a list,
and a source for that list is our list of years.
Okay, there we go.
So we've got 2016, 17, and 18,
which are selectable in that cell.
Now from there, let's actually create
the average rating calculation
here in H7 based on that year.
So we could start with something simple,
like average of C4 through C13.
Close it off, we get 6.54.
If we highlight those cells, check the footer,
that checks out, but obviously not dynamic yet.
What we really need is this calculation to update
and average each different cell range
based on the year selected.
So to get started, we can place our cursor right here
after the average function and we can insert a choose.
And we don't know what the index number
is gonna be quite yet, so let's just put a number there,
number one to start, and now we need
the three values in our list.
So instead of just adding traditional values,
like 2016, 17, 18, the items in our list now
are actually gonna be cell ranges.
So the first item is C4 through C13.
The second item is our 2017 rows, D4 through D13.
And the third, E4 through E13.
Close off one more parenthesis, press enter,
and we still get the correct result,
but there's one more step, right?
Because this index number is still fixed at one,
we want that to relate back to which year was selected.
So if a user selects 2016, we wanna choose the first item
in the list, which is this range from C4 through C13.
If they choose 2017, we want a 2.
If they choose 2018, we want a 3
to pull out the proper reference
from that list in our choose statement.
So there are a few ways to do it.
One of the easiest ways is to use a match function.
So we're gonna replace that one with a match function,
and we're gonna say, "Hey, match the year
that someone has selected and try to find it
in that list of three years."
That way, if they chose 2016,
it's gonna be the first item matched,
which returns a 1, 2017 would return a 2,
and that gives us the index that we need.
So I'm gonna comma over, choose exact match here,
close the parenthesis, and press enter.
So, so far, so good, looks good there.
Let's change the 2016 to 17, 6.42, 2018, 6.57.
Do a little spot check, 6.57.
So we are good to go.
We've used average with a choose function
and a match to aggregate those values
from three different cell ranges,
which is a pretty cool trick, but we can do better.
Now let's actually make the calculation itself dynamic.
So in this case, we want to calculate an average,
but what if I'm also interested in understanding
the maximum rating or the minimum rating for any given year?
Well, we can do that by allowing user to select
one of three different calculations to make.
So let's use data validation again.
We're gonna allow a list, and it's just gonna contain
three items that'll type in by hand here.
Average, AVG,MAX,MIN.
Press okay.
And I've got those three different types of calculations
stored right here in the data validation cell.
And now we're gonna try to tie that to our calculation cell.
But before we start writing choose,
remember that we're gonna need a way
to make our index dynamic.
And we can't really use any sort of match function
like we did with the year, because we don't really
have any other instances of these labels
that we can draw from.
So what we can do here is use a little helper cell.
I'm gonna use I5, and just write an if statement
that says if that cell, H5,
equals average, then return a one.
Comma over to my value if false.
Another if statement, if H5 equals max, then return a two,
otherwise return a three.
Close out two parentheses, press enter,
and let's test that that works.
One, two, three, beautiful.
And now that we have this index
that's tied to our selections,
we can plug that into the index argument
of a choose function and then build out
our list items accordingly.
So because we're no longer just calculating
average rating here, we can go ahead
and update that label to something like equals
whatever you've selected in H5,
and in quotes, the text string rating.
There we go.
So now that'll change from average rating to max rating.
Whoops, to min rating.
Looks great.
All right, last step here.
In H7, we don't wanna really reinvent the wheel here
because we have a great average function
that's working beautifully.
So let's go ahead and work efficiently and keep that
and wrap the whole thing in one more choose.
And now we're gonna say. "Let's choose
one of the following calculations
based on this index number we created."
So our index is gonna be I5,
and now we enter our list of items.
So remember, average is one.
So the first thing we want to do
with this choose function is calculate the average,
which is exactly what we're doing here.
And now all we need to do is grab that whole thing,
copy it, add a comma, now we're in item two.
I'm gonna paste that, change the average to a max,
which is our second type of calculation.
Add one more comma, paste it again,
change the third average to a min,
and then close off one parenthesis,
press enter, and things are looking good.
Okay, so average rating from 2018, 6.57.
We know that's correct.
Let's try changing that to the max, 7.22.
There it is, that looks right.
How about the minimum from 2017?
4.7, boom, accurate.
So that all looks great.
It's working perfectly.
And the choose was a really great option here
to help us do this efficiently and dynamically.
So because we don't really need users
to see this index column or this index value here,
one of my favorite tips, I can format that,
use a custom option, do the old triple semicolon trick,
which makes it invisible.
Still there, just invisible.
All right, final task for this tool.
Now we're gonna actually practice embedding a choose
into a cell reference itself, which sounds crazy.
It's one of the only functions that can do this.
So to show you how this works,
let's say we want some sort of like a rolling average.
So I'm gonna type a year average here,
and we'll start with a 1 in cell F2.
So a one-year average means that I just want to take
the average of the current year, 2018, okay?
If this is a two, it means I want to take
a two-year average, 2018 back to 2017.
If this is a three, it means I want a three-year average.
So this is a great place to use the choose,
and more specifically, to embed the choose
into a cell reference itself.
So here's what we're gonna do.
We're gonna start with an average function, okay?
And we know that one half of the reference
is always gonna be 2018, right?
Cell E4.
And the only thing that's gonna change
is we either want to average just E4,
or we want to average E4 and D4
or E4 all the way back to C4, okay?
So I'm gonna type the colon and delete
the second half of that reference.
So just got the first half.
And now check this out.
I can just add the choose function
right here inside of the cell reference and say,
"Okay, let's choose the third item from this list,
or whatever the value currently takes in cell F2."
I'm gonna press F4 to fix that
so I can apply this formula down.
And now out of my three options that I have,
the first item represents a one-year average,
which means I just wanna average E4 through E4.
The second is a two-year average,
which means I want E4 through D4.
And the third is the three-year average, E4 through C4.
And yes, if you're wondering, you can use a backwards range
that goes from E backwards to C, it'll work just fine.
So we'll close off two parentheses, press enter.
Three-year average, 6.48.
Check the footer, looks good.
Two-year average, 6.43, looks good.
One-year average, 6.41, beautiful.
So let's change that back to three.
We can drag that all the way down and it works perfectly.
So there you go.
That's your demo on the choose function.
May not be one of the most well-known functions,
but can be an absolute lifesaver when you need it.