Instructor: All right, pro tip time.
This is a fun one.
In fact, it's something I actually just learned recently
which is that you can join arrays using Choose.
Now, the Choose function, which we've talked about before
can actually be used to combine separate cell ranges
or spill ranges into one single array.
And that means it can be referenced
or manipulated as a single unit within other formulas.
So here's a common use case when this can really
come in handy.
Suppose you've got raw data, it's at the product level.
You got product names, category names
sales and margin values.
And maybe you do something like this.
Use a unique function to come
up with a unique list of categories.
And then based on those five categories
you calculate the average sales
by category using something like average if or average ifs.
Now, that's nothing new.
It's an approach that we've used a number
of times already in previous demos, but the catch is
that Excel treats these two spill ranges in columns F and G
as two distinct arrays and that limits how we can manage
and manipulate the data in those two columns.
This is where choose comes in.
So it's a little tip.
You can use the choose function.
And in that first argument, the index argument
you can use an array constant like one comma two
and then point to each individual spill range
that you want to combine into one array.
Here we have two spill ranges
or arrays that we want to combine F two pound
and G two pound.
So we use an array constant of one comma two
reference each of those two arrays or spill ranges in turn
and then that function essentially spits out
a single array that consolidates all
of that data together.
So that's the basic primer on how choose is used
in this context.
To really drive it home, let's shift gears.
Let's jump back into our Excel workbook
and I'm gonna show you when
and why this can be really, really powerful.
Alright, so go ahead and open up your dynamic array
formulas workbook, head to the choose and let tab.
And here we've got the same raw data
that we've been working with.
Nothing new there.
Got some placeholders that we're gonna fill out.
And basically we want to start by calculating the
average salary at the industry level.
So we're gonna start by just producing the unique list
of industries using unique function.
Nothing special here.
There we go, right?
We don't even need to sort it, I'm not worried about that.
Next, I wanna calculate the average salary for each
of these industries.
So I'll use an average ifs, my average range.
Well those are my salaries.
D three through D 842.
My criteria range, my full list of industries in column A.
And the criteria itself is the spilled range
that I just created.
G three pound, close it off, press enter.
There we go.
So we've done this before.
This is not new.
But suppose we now want to sort this data that we
just created in columns G and H
descending by that calculated average salary field.
Let's see if we can give that a shot here in column J.
Okay, we'll start with a simple sort function
and we're gonna sort that array that we just created.
Sort index is the second column, average salary
and we want it descending with a negative one.
Press enter.
And there you go, right?
We've got our unique industries.
They're sorted descending
by that average salary that we defined
with an average ifs function.
But there is one problem with what we just did
and I'm wondering if you caught it.
Note that
in our search formula when we inputted that first argument
the array argument
we don't see a spill range reference here.
We see a fixed cell range from G three through H 19.
In fact, if I delete these arguments and start over
I'll see what happens.
If I just select column G, I get a spill range.
If I just select column H, I get a spill range.
But when I try to select both together
I don't get that spill range, I get the fixed reference.
So I'm gonna press escape here.
Now, if the data were static
if it were finalized, this may be okay.
But the problem is if new industries get created
or those spill ranges in columns G and H grow, right?
Let's say there's a new industry called Tech two
press enter.
Notice that our spill ranges in column G
and H both expanded to accommodate that new industry
but our sorted array here in J
and K is missing one of those industries.
And that's because our sort function was fixed
to that specific cell reference.
So let me undo that change.
We'll get it back to technology.
And this is exactly the type
of use case where this choose tip can be really helpful.
So instead of the sort approach
I'm gonna delete what I just did.
Instead, I'm gonna use a choose function here.
And remember, for the index number argument
we're gonna use an array constant
with one comma two, close the curly bracket.
And now I'm literally just gonna input the two spill ranges
or dynamic arrays that I wanna join together.
First is this one G three pound second, H three pound
close it off, press enter and check it out.
You see that blue border surrounding this
as one single cohesive array.
And of course, if I want this sorted now
I could simply wrap that choose function in a sort.
Again, I'm gonna sort the second column descending
with a negative one.
Press enter and boom, there you go.
So I've got my unique industries sorted
descending by a calculated field that I created
and then joined together using that choose function.
But you may be thinking to yourself, you know, that's great
but you know, we had to create, you know
this array in column G, this other array in column H.
Treat them like helper columns
and then join them here in column J.
Well this is a great excuse to nest
these functions together.
We can accomplish this exact same output
within one single formula, just like we did
with those dynamic top end calculations with RAND array.
So in this case, let's start with our formula in J three
and instead of referencing our spill ranges G three pound
and H three pound, we can go ahead and take those formulas
and embed them into this one single choose function.
So I'm actually gonna start
with my H three function, copy the whole thing,
jump back into J three and replace
the instance of H three pound.
With that whole function, press enter.
Still looks good.
And now I want the G three spill range function
which is my unique function.
Copy that one.
Head back in and you can see I've got two instances
of G three pound that I'm gonna replace
with that unique function.
Press enter.
There we go.
And now, if we were to delete these columns
or delete these formulas, it doesn't matter
because we've accomplished the same thing
using one single formula here in J three.
So let me undo those changes just
so that we can have these here
as a building block for reference.
There you go.
We've combined a dynamic array function
like unique with a legacy function like average ifs.
We've joined those arrays with Choose
and we've nested it all together to create this output
in columns J and K.
Really, really powerful stuff.