Ranking

John20

New member
Joined
Aug 4, 2023
Messages
13
Reaction score
0
Points
1
Excel Version(s)
2010
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
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.
 
Back
Top