Form Rank & Class Rank?

[tinfanide]

In the attached file, I have a score table of Form 1.


There are 1A, 1B and 1C classes.
I used RANK to compare the whole form scores (to get the form rank of each student).
But how can I use RANK to compare the class scores (to get the class rank of each student)

Please see the attached Excel file. Thank you!
_rank.xlsx

[excelledsoftware]

You need to convert the Letter into a number. The CODE formula will do this.

Place this formula somewhere next to your data and fill it down (You can hide this column after)

=SUM(0+LEFT(B2,1)&CODE(RIGHT(B2,1)))

This basically takes the first character and then converts the 2nd character to a number then it put it in a sum + 0 to convert it all to a number that you can then use your RANK formula with.

Let me know if you have any other questions.

By the way looking at your formula before you can make it easier by typing it like so and then autofilling or copying/pasting it down

=RANK(C2,C$2:C$6)

The $ sign locks that cell reference and it will not change when filling down.

Here is an attachment if the above didnt make sense.

[tinfanide]

Thanks for your quick reply.

But maybe I've made myself not clear. In the attached Excel file, the highlighted (orange) column will be the one I've intended to do (the result I want Excel to return).

The class rank is the rank which is arranged against the class only, not the whole form.

Could you have a little look at the Excel file (_rank2.xlsx)
_rank2.xlsx

Thank you!

[excelledsoftware]

At first I didnt quite understand but now I do. You want a rank within the class not ranking the actual class. Hmmn apart from applying a sort and then having a formula reference just those cells I am unsure how to accomplish this with formulas. a VBA script could certainly do it but I am trying to not suggest those as much as some people only want simple solutions. Anybody else have an idea of using a combination of sumif to rank each value within the class?
Otherwise I think VBA is your answer.