Calculating Class Ranks and Their Percent Rank

A

alessandroar2

New Member
Joined
Jun 30, 2010
Messages
2
Hi,

I work at a University and I am trying calculate the students ranks based upon their GPA. I understand how to use the Rank function to determine their numerical ranking, however I need to find out each students percentage as they stand in their class.

For Example, the student with the highest and best GPA is the Top 1% of his or her class. Is there a way for Excel to automatically calculate their percentage ranking for each student, rather than having to type it in manually?

My current approach is do a percentile function 'PERCENTILE($X$186:$X$320,.99)' which will give me a GPA corresponding to that Percentage. I then have to take that GPA and manually type in the 1%, 2%, 3% and so on until I have inputted the % Ranks for each student. There must be an easier way, correct?

Thanks for your help!

Tony
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Sort by date Sort by votes
So GPAs are in column A, rows 2 to row 1000.

=Rank(A2,$A$2:$A$1000)/Count($A$2:$A$1000)

if the person rank 50 out of 150, the answer is 33%.

Hope this helps.

Jeff
 
Upvote 0
Thank you, that was extremely helpful!

There is one slight problem however. Some of the percentages that it returns is slightly different from my Percentile calculations (I use '=PERCENTILE($AH$5:$AH$255,0.9)' to calculate who is in the top 10%. On some of the %'s that the formula '=RANK(AH5,$AH$5:$AH$255)/COUNT($AH$5:$AH$255)' provides are off by 1% in some areas. Is this just an estimation thing? Or am I doing it wrong.

Regardless, thank you so much Jeff. This still helps out Greatly!
 
Upvote 0
Under the HOME tab, there's a tool box called Number. You should see the buttons called "Increase Decimal" and "Decrease Decimal." This will allow you to increase/decrease rounding. Just highlight your column/rows that you want more precise and click Increase Decimal.
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question
Average/rank with blank cells
Replies
2
Views
552
lonewaya1984
L
J
  • Solved
Average Rank from multiple columns?
Replies
3
Views
513
Fluff
Fluff
B
  • Solved
Return text from Column A based on Rank of Column B which has duplicates
Replies
5
Views
2K
Fluff
Fluff
A
  • Question
Unsure why getting #VALUE error in a AVERAGE(IF(SUBTOTAL... formula
Replies
5
Views
482
Fluff
Fluff
M
  • Question
Creating a Calculated Field / Measurement to obtain both a weekly grade and a final grade for English classes
Replies
0
Views
359
MiedodeEncontrarme
M

Forum statistics

Threads
1,214,985
Messages
6,122,607
Members
449,090
Latest member
vivek chauhan

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top