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.

thanks a lot!

Thank you

Thanks! This helped me on my homework. I was stuck because I was getting the same issue of multiples of the same rankings w/ different values as you explained at 1:53. Is this unique to version 2013?

This was really helpful. Thank you so much!

Thank you so much , you exactly solved my problem :))

Thanks, but one problem is that, if two scores are same then two ranks are same but next rank is not shown, for example scores (72.7, 72.7) rank (6, 6) but rank 7 is not shown. When i apply this formula on result sheet for positions the i face problem, please help.

please help to rank the numbers after repeated values

Solved my problem thankssssssssssssssssssssssssssssssssssssssssssssss

Hi Good Morning

Is there any method to get the ranks as follow:

34 to 1

32 to 2

32 to 2 (not 3)

30 to 3 (not 4)

28 to 4

…

We created a spreadsheet for a competition. What we need is when the score is same we need the same rank (prize) but no skipping in the rank as well.

Thank you in advance

What if my numbers are in horizontal ,

like :

A B C

11 21 31

and I want to rank in a normal vertical way

ranking normally like

A 3

B 2

C 1

Thank you