Instructor: Alright, so we've introduced
the sequence function,
we've talked about syntax, shown some basic examples.
Now let's roll up our sleeves, jump into Excel,
and actually practice putting this function to work.
So open up your Dynamic Array Formulas workbook,
head to the Sequence tab, and let's give this a shot.
Now, first things first,
let's just use some blank real estate here
and get comfortable writing some sequence functions
and interpreting the outputs.
So we could start with a very simple sequence function
that just uses the required rows parameter.
Let's say 10 rows.
Close it off, press enter,
and what that's gonna produce is an array of values.
That's one column wide.
It starts at one and it increments or steps by one.
So we get 10 rows tall, one through 10.
If we wanted to produce a two-dimensional array,
we could very easily
just add a second parameter to the formula,
and say, okay, we want three columns wide.
Close it off, press enter.
We're still starting from one,
we're still stepping or incrementing by one.
Now we just have a three-by-10 array
that extends from one to 30.
And last thing here, we can update the final two parameters.
Say, you know what,
instead of starting at one, let's start at five,
and instead of stepping by one, let's step by 20.
Close it off, press enter, and there we have it.
We start at five, we move left to right,
incrementing by 20 each time,
and then go down to the next row,
left to right, down to the next row, and so on and so forth.
So there you have it.
That's a basic sequence function
and the output that it produces,
but like we talked about, it's very rarely used on its own,
and the example that I wanna show you here
is how we can combine it with a large function
to extract the top N salaries from some source data.
So just like we've been dealing with
throughout this project,
we've got the same kind of raw data here.
We've got the industry column,
job title, cities, salary, and growth rates,
and our first demo here in column G
is to pull out just the top three salaries from column D.
And to do that, we can use our handy large function,
only two arguments here.
The first is the array of values, which lives here,
D3 through D842.
Second argument is K, which tells Excel
I want the first or the second or the third largest value
from that range.
In this case, we want the first
or the largest overall value.
So I'll type a one for the K parameter,
close it off, press enter, and there you go.
It spits out 155,654,
which is the highest salary in our source data.
But here, we don't just want the top salary.
We actually wanna see the top three, okay?
So if we're dealing with traditional Excel,
what we might do here is fix this reference,
copy the formula, paste it two more times,
change the one to a two and then to a three,
and now we have the first largest salary,
the second largest, and the third largest,
using these three formulas.
But notice what's changing here.
It's just that argument from one to two to three.
It's almost as if we're creating a sequence of values
from one to three,
and that should give you a hint
that that dynamic sequence function
can be nested in here to make this formula more dynamic.
And that's exactly what I'm gonna show you here.
So first things first, let's clear out the space,
delete these other two formulas, jump back to our first one,
and now instead of hard coding the one,
this is where I can nest a sequence function.
And remember, just like I showed you,
we basically want to produce a one-column array
from one through three.
So all I need is this first argument here, three rows tall,
close off the parenthesis, press enter, and boom,
we get the exact same results
using dynamic array functionality
and only using one single formula instead of three.
So that's great, but we can do even better, right?
We can make this formula even more dynamic
by turning it into a top N calculation
instead of fixing it to top three.
And so let's actually just copy this formula,
paste it right down here,
and this time, instead of hard coding the three,
we can reference a cell, like J3,
where a user can actually determine the number of rows,
and therefore the number of top salaries to return.
So I'll press enter here,
and now because we have a five in cell J3,
we get the top five salaries.
Change this to a 10, and we get the top 10.
So again, pretty awesome that we can do this so easily
using this dynamic array functionality
and the sequence function.
But suppose we wanna take it even further, right?
Using this combination of large in sequence is great,
but it limits us, right?
We can return the top 10 or top N salaries,
but we've lost the context, right?
We don't have the other associated values from our raw data.
We don't know which job title it is
from which industry, which city it represents,
and that's really important context
that an end user is likely gonna want to see.
So how could we go about doing that?
Let's start from scratch here and sell K6.
The first thing that we're gonna want to do is return values
from our raw data,
filtered down based on the salary criteria.
In plain English, what we want to do is return any records
of industry, job title, city, salary, and growth,
where the salary value is in the top 10, okay?
So let's just start with filter,
and we wanna return data from our entire range,
so all of our headers,
so we can grab everything from A3 all the way down to E842.
And here's the kicker.
This is where it's a little bit challenging.
Which records do we want to include?
Well, again, in plain English,
we only want the records
for cases where the average salary is in the top 10.
Okay, so we know our filter criteria
is gonna be based on column D,
so let's go ahead and select D3 through D842,
and the actual condition here that we need to be true
is that those salaries need to be greater than or equal to
the 10th largest salary,
and that's gonna return our top 10
based on whatever number has been entered in J3.
Okay, so we're gonna say return records
where column D is greater than a large function
referencing those same salaries, D3 through D842,
and the K value here in this case is 10,
but we want it dynamic,
so I'm gonna point to sell J3,
close the parenthesis one more time, and press enter.
So, so far so good.
We got 10 results here.
We're pulling the industries, the job titles, the cities,
the salary, and year over year growth rate.
The only catch is that it's not sorted
to map to those average salaries from high to low.
So what do we do when we need to sort a dynamic array?
Well, we use the sort function.
So we'll wrap everything we just wrote in a sort function,
jump to the end of the formula
where we can get to our next argument.
The sort index is our average salary column,
which is in the first, second, third,
fourth column in the array, so I'll type of four,
and the sort order, in this case,
we want high to low, so descending,
which I can note with a negative one.
Close that last parenthesis, press enter, and check it out.
There we go.
So we can map up or match up our average salaries here
with the average salaries returned in our filter function,
and now we have all of that additional context.
That top job is an attorney title,
which is in the law industry from San Francisco.
We've got all of that context
for all of our top 10 salaries.
And as always, we can adjust this to top five,
top two, top 15, top eight, whatever we choose to do.
So there you have it.
Really good example of how to nest a sequence function
with other dynamic arrays
to do some really cool things.