Instructor: Alright, so we're gonna talk about
a few more functions now.
We're gonna talk RANK, SMALL, LARGE and PERCENTRANK.
So I'm gonna start with the SMALL and LARGE functions,
which return the nth smallest or largest values
within an array.
Formula syntax wise, there's two components.
You have your data array
and then the rank of the number you're trying to return.
So in this case, if I write LARGE A2 through A8,2,
it's gonna look for the second largest number
within the array A2 through A8.
It looks at that array, it finds that 90
is the second largest number, and it returns 90.
Same story with SMALL, it's just kind of flipped.
So if I write SMALL A2 through A8,3,
it's gonna look for the third smallest number in the array
which in this case is 50.
RANK works in a similar way.
It kind of gives you a similar insight.
Again, two components of the formula.
You start with kind of the cell of interest
or the value that you're trying to find the rank of
followed by the array in which that value lives.
So by writing Rank A2, A2 A8,
we're looking for the rank of the value in A2
in the context of the array from A2 through A8.
So that would return a two, because the value in A2,
which is 90, is the second highest value in that array
and it kind of follows from there.
You'll note that rank of A5 within A2 through A8
equals one, which indicates that it's the highest value
in the array.
And the rank of A3 gives me a seven,
which indicates that it's the lowest value.
Next, the PERCENTRANK, it works just like RANK,
except it returns the rank of a value as a percentage
of a given array or data set.
So this formula syntax is a little bit different,
but again pretty simple, just two components.
It starts with the array.
So what range of data are you looking at?
And then the second piece is just noted with an X
and that's which value within the range do you care about.
And so it's gonna tell me what the percent rank
of that value X is within the context of the given array.
So if I wrote a PERCENTRANK formula in column B
here in this little screenshot, I'm gonna see 100%
for the value that's the highest in the array.
So in this case, it's A14, which is 6,827.
You can see that it's the highest value here
which corresponds to a 100% percent rank.
Same thing, the lowest value will return a 0% percent rank.
So let's jump into Excel
back to the basic stats functions tab where we left off.
So for the 10th highest salary
I'm gonna use a LARGE function here.
So equals LARGE.
My array, it's going to be the salary column
and then I want the 10th largest, so I'm gonna write 10.
So $24,642,857 is the 10th highest salary
in my dataset.
Same deal.
To find the hundredth lowest salary
I'm gonna use the SMALL function.
My array, again, is gonna be column E
and then type 100.
So 405,000, which is just above minimum
is the 100th lowest value.
So there you go.
There's small and large.
Let's jump to the salary data tab
and fill in columns F and G,
which are to calculate rank and percent rank.
So what I wanna do is find the rank
of each associated salary as part of the larger range.
So for this one, I'm gonna start with =RANK.
So it's asking for the number,
which in this case is cell E2,
and then the reference which is the full array
in which it lives.
So here's an important distinction.
First off, I'm gonna use a shortcut that I taught you
in the first section where I'm gonna select E2,
hold CTRL + Shift + and arrow down
to give me the entire contiguous data array.
And the other important piece here that I need to do
is fix this reference.
So without touching anything, I'm gonna press F4.
And as you can see, it fixed that entire array
from E2 through E4135.
But I'm leaving the E2 as a relative reference
and you'll see why in just a minute.
So if I close it out
I'm done with my rank formula, I'm gonna hit enter.
This is telling me that this first player
I think it's Bobby Abreu in 2010
as the 4014th highest salary in the dataset.
So now, because I set my references and thought about them
as I was writing the formula, I should be able to just
double click to apply this formula down.
And as you can see, the only thing changing
in my formula is the reference to my value.
So that's what I want to be changing,
but I don't want the entire array to be shifting down
or changing as I move this formula.
So this is working properly.
PERCENTRANK.
I'm gonna do a really similar thing here.
I'm gonna type =PERCENTRANK.
It kind of is flipped.
So I'm gonna start with the array.
Click E2, CTRL + Shift + arrow down,
fix it with F4,
comma to the X, which is the value that I care about.
I'm just gonna page over to E2, close it off, press enter
and apply it down.
So there you go.
If you look at percentage rank
and compare it against salaries,
you'll see that it makes sense.
So the higher percentage ranks 86.3% for instance,
is tied to a salary of 8.25 million,
whereas the lowest salary is 400,000
equate to a percent rank of zero.
So there you go, SMALL, LARGE, RANK, and PERCETNRANK.