Ranking

John20

New member
Hello everyone
I am a teacher.I want to rank student's scores. Sometimes they're in 2 classes and sometimes they're in 3 classes. Each class has 20-25 students. How can I rank their scores? Each student must be rank with his class. I don't want to use table.

Thanks a lot
Notice this point please.
If two students have the same rank for example(1), the next student have 2, not 3.

Hello everyone
As I mentioned in last post, If two students have the same rank for example (1), the third student must be 2, not 3. can you help me?

I suggest you post a sample workbook that accurately reflects your data layout (but with fictitious data) and explain what tables have to do with anything, please.

I suggest you post a sample workbook that accurately reflects your data layout (but with fictitious data) and explain what tables have to do with anything, please.
Thank you
I want to rank student's scores. Sometimes they're in 2 classes and sometimes they're in 3 classes.How can I rank their scores? Each student must be rank with his class. If two students have the same rank ( 1), the third must be rank 2 not 3. Now they are in 3 groups but may be in 2 proups.
Is there a way to change the groups dynamically when they increas or decreas?
I don't want to use table.

Attachments

• Ranking.xlsx
9.4 KB · Views: 4
You could use a formula like:

=SUMPRODUCT(IFERROR(((C2<=\$C\$2:\$C\$37)*(\$A\$2:\$A\$37=A2))/COUNTIFS(\$C\$2:\$C\$37,\$C\$2:\$C\$37,\$A\$2:\$A\$37,A2),0))

also, if you have a more recent version of Excel:
=MATCH(Table1[@Scores],SORT(UNIQUE(FILTER(Table1[Scores],Table1[Groups]=Table1[@Groups])),,-1),0)

the same without having to make the table into a proper Excel table:
=MATCH(C2,SORT(UNIQUE(FILTER(\$C\$2:\$C\$37,\$A\$2:\$A\$37=A2)),,-1),0)

Attachments

• ExcelGuru11761Ranking.xlsx
12.3 KB · Views: 2
You could use a formula like:

=SUMPRODUCT(IFERROR(((C2<=\$C\$2:\$C\$37)*(\$A\$2:\$A\$37=A2))/COUNTIFS(\$C\$2:\$C\$37,\$C\$2:\$C\$37,\$A\$2:\$A\$37,A2),0))
Thanks a lot.

also, if you have a more recent version of Excel:
=MATCH(Table1[@Scores],SORT(UNIQUE(FILTER(Table1[Scores],Table1[Groups]=Table1[@Groups])),,-1),0)

the same without having to make the table into a proper Excel table:
=MATCH(C2,SORT(UNIQUE(FILTER(\$C\$2:\$C\$37,\$A\$2:\$A\$37=A2)),,-1),0)
Thanks a lot.