This is Stephanie from StatisticsHowTo.com, and in this video I will show you how to use
the Rank function in Excel 2013. I have a list of students and their grade
point averages. I want to find out what their class ranks
are. Click an empty cell right next to the first
data point. Type =rank and you are given three options
in Excel 2013. The very last function, the Rank function
is now obsolete, that is what was previously used in Excel 2007 and earlier.
So if you want your work sheet to be compatible with versions of Excel 2007 or earlier then
you need to use the rank function. The equivalent rank function in Excel 2013
is the rank EQ, so I double clicked on that. And it is giving me the inputs, number, ref
and order. Well if you are not sure what those mean,
click the insert function button on the toolbar. And it gives you a better description of what
that is exactly. Number is the numbers for which I want to
find the rank that will be my first score. Ref is the reference toour list of numbers.
I am going to select all of my numbers. And the order, the descending order is zero,
any non-zero value would sort in ascendiing order.
I am just going to go ahead and sort in descending order.
So I put in zero, and OK. And that first score is ranking at 2.
And I am going to drag the formula down. And right away I can see there is some kind
of problem with my formula because I have got several ranking at one.
So if you are going to take a look at my last cell.
I see that Excel has copied the formula, but it is going down ward with this center part
of formula. I don’t want it compare to the values of
B11 to B20 because it is nothing in there. So I am going to go back up to my first cell.
And I am going to lock this center part of the formula.
Click on one of those cell locations. Hit F4 and it locks the formula.
Hit F4 again to lock that second part of the formula, and now I can drag the formula down.
And there are my correct rankings. The second rank function, rank average.
That is something slightly different, still has the same inputs.
This time I am going to remember to lock my formula and drag it down.
If you notice we have got two students who scored the same, 72.7.
With rank EQ it assigns an equal rank to both students, and that rank is 6.
It skips 7 and goes up to the 8th rank here with Tray.
We would rank average, it takes the average of the rank not the average of the score but
the average of the rank Well they are actually scored 6 and 7, there
average is 6.5 so the rank here is 6.5. Which formula you used depends on what kind
of results you want from your data. The second way to find the rank is on the
data analysis tool. Click data.
Click data analysis. If you do not see data analysis, install the
data analysis toolpak. Choose rank and percentile, click OK.
My input range, I am going to select my entire list of scores.
They are grouped by columns, and I did not include the label in the first row.
I selected outside of that. My output range, I want it to go in this next
column here, click OK and here are the result of the data analysis.
The rank is in the third column here, it matches the results given from the rank EQ function.
In the last column are the percentiles and I will cover those in the next video how to
use the Excel percentile function. Visit us at StatisticsHowTo.com for more Excel
and statistics tips. Thanks for stopping by.