Instructor: All right,
so the first Dynamic Array Function
that we're gonna talk about is the Sort Function.
And again, Sort just sorts an array of data
by one or more columns within that array.
And syntax wise, pretty simple.
In fact, there's only one required parameter or argument,
which is the array of cells that you want to sort,
followed by three optional parameters.
You've got the Sort Index, the Sort Order,
and the By Column argument.
So, Sort Index is the column number within the array,
that you want to sort by.
If you leave this blank, the Default Behavior is a 1,
which means Excel will sort by the first column
within that referenced array.
Next up, you've got the Sort Order Ascending or Descending.
The Default Behavior here is a 1 for Ascending.
And finally, you have the By Column argument,
which by default will be False or Zero,
meaning that you're sorting row-by-row, top to bottom.
But if your data is pivoted or transposed,
you put a True or a 1 parameter here,
to Sort by Column, left to right.
In my experience, 99% of the time,
I'm gonna be sorting top to bottom, row-by-row here.
which again, is Excel's Default Behavior.
So quick visual example here.
Suppose you're looking at Raw Product Data
in columns A through D.
You've got Product Names in column A,
Sales Figures in column B,
Margin Percentages in C, and Profits in column D.
You could use a function like Sort to say,
"Hey, I wanna sort this whole range A2 through D10.
I wanna Sort by the fourth column within that array,
which is Profit.
And I wanna sort those Profit Values Descending,
by specifying a -1 for the Sort Order."
As a result, you get something like this.
Now we're looking at a list of Products,
Descending by Profit, high-to-low.
But we can get a little bit fancier than that.
And let's look at a second example of that function.
This time we're using something called Array Constants.
And you'll recognize Array Constants,
because it's numbers surrounded by curly brackets
inside of the formula.
And what Array Constants allow us to do
within a Sort Function is actually define a Sort Order
for multiple columns.
So in this case, what we're doing is we're sorting
that original array from A2 through D10,
by the third column first, which is the Margin column.
And then after we've sorted by Margin,
for any cases where the margin is the same,
we're then sorting by the fourth column in the array,
which is Profit.
And we can use another Array Constant
for our Sort Order Parameter to tell Excel
that we wanna sort Margin, Ascending with a 1,
and Profit, Descending by a -1.
And this can be a little bit tricky
to wrap your head around at first.
But let's jump into our Excel Workbook
and actually practice applying these Sort Functions
to some real data.
All right, so go ahead and open up
your Section Nine Workbook.
We're now in the second tab here, the Sort Tab.
And as you can see, we've got the same data
that we were working with previously.
A list of Industries with Average Salaries,
and Year-Over-Year Growth Rates in columns A through C.
And we're gonna practice sorting
that data in different ways.
And let's start really simple,
by just returning a sorted list of Industries.
Well, we can use a Sort Function.
And the array here,
because we don't care about the Values in Columns B or C,
can really just be this fixed reference
from A3 through A19.
And we're dealing with static data here.
We're gonna continue dealing with static data
throughout the rest of these demos.
Now, so no need to worry about formatting as Tables,
no need to worry about Named Ranges.
We're gonna try to keep it pretty simple
and focus on the formulas themselves.
So we're simply gonna sort that array of Industries.
And I actually don't need to input any other arguments here.
I can close it off and let Excel choose the Default Behavior
and press Enter.
And there you have it.
I've got an alphabetically sorted list of Industries.
So now over here in column G,
now I wanna return the whole range,
including Salary and Growth Rate.
And I wanna sort the Industries
by Average Salary from highest-to-lowest.
Well, I can use Sort again.
This time, instead of just column A,
I wanna return the entire array of data from A3 through C19.
I do wanna specify a Sort Index here,
because I'm not sorting by the first column,
which is the Default Behavior.
I wanna sort by the second column, which is Average Salary.
So I can use a Sort Index here of 2.
And then the Sort Order,
again the Default Behavior
would return Industries sorted, Ascending
by Average Salaries with the lowest salaries at the top.
In this case, I wanna see the highest salaries at the top.
So I want this Sort Order, Descending instead.
I could specify a -1.
Close it off.
'Cause I don't need the By Column argument here.
Press Enter, and there you have it.
I've got my list of Industries from my Source Data,
Descending by the second column, Average Salary.
Very simple, very straightforward,
but also a very powerful tool.
Last but not least, let's practice using some
of those Array Constants.
This time we wanna return the top Industries,
first, by the Growth Rate,
and then by Average Salary,
in cases where the Growth Rate is the same.
Okay, so we can use a Sort here.
Again, we're gonna Sort that same range A3 through C19.
And this time, instead of a simple Sort Index of 1, 2, or 3,
we're gonna use an Array Constant
to tell Excel to sort by
the third column first, Growth Rate,
and then the second column, Salary.
So I'm gonna type an opening curly bracket here,
specify the third column index first, comma,
second one, close the curly bracket.
And that's my Array Constant to specify the Sort Index,
which columns I wanna sort by.
Again, last argument here is the Sort Order.
If I wanted to sort both those columns the same way,
in this case, Descending,
I don't need another Array Constant.
I can simply type -1,
close it off and press Enter.
And check it out,
it's sorting first by Growth Rates, which looks right.
And in cases where Growth Rate is the same,
we're sorting Descending by Average Salary.
All right, 26.
72,000 down to 66.
82 to 39.
That all looks good.
And if for whatever reason,
we wanted to change the Sort Order
for just one of those columns,
let's say we wanted it Ascending by Salary instead,
we can simply modify this formula
and use a second Array Constant,
and say we wanna sort column three, Descending,
but column two, Ascending with a 1.
Close the curly bracket, press Enter.
And now, basically those values have just swapped.
So we've got 49 then 62.
66 then 72.
39 then 82.
And just like our previous examples,
even though we're returning
a two dimensional array of values,
we're only using one function to do so,
and that's where we initially entered
our Dynamic Array Function, in this case, in K3.
So hopefully, this starts to give you a sense
of just how powerful these formulas really are.