Rank for different Criterion Groups

amkkhan

Board Regular
Joined
Dec 11, 2021
Messages
75
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Is it possible to get different set of rank for different criterion groups in the same column.
Actually I wish to get each exams rank to be compared with one of its own kind for each student.
For example rank of 1st-Term should be compared with results of 1st-Term of all students.
In the same way the rank of Mid-Term should be compared with results of Mid-Term of all students.
Same for rest of exams and

rank of Grand-Total should be compared with results of Grand-Total of all students.

And essentially the rank that a student is achieving after final term in "Grand Total" row.
My apologies XL2BB is not working at the moment. If you are coming with solution please also post XL2BB
 

Attachments

  • Rank.JPG
    Rank.JPG
    76 KB · Views: 12

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
I wish there was any way to share long sheet.
You could share the whole workbook on Dropbox, Google Drive or some other file sharing site, but let's see if this works first...

I see you have Roll No. 4 with the name "Grand Total". That accounts for why their Grand Total rank is 5 out of 4 students. As @Marcelo Branco has said it would be best if you did not merge cells which will be the subject of a formula. I have changed iit to "James" as per your original sample.

I have changed the columns to match your posted image but am sharing only those which are used in the Rank. My formula in column AE is ranking by the percentage in column AA.

Amkkhan.xlsx
ABCAAAE
8Roll No.NameExam%ageRank
91Joe1st Term30.863
10Mid Term49.823
112nd Term30.863
12Final Term35.823
13Grand Total44.293
142Nick1st Term66.291
15Mid Term69.821
162nd Term80.001
17Final Term48.912
18Grand Total59.512
193Sunny1st Term66.291
20Mid Term50.362
212nd Term66.292
22Final Term60.551
23Grand Total60.101
244James1st Term17.144
25Mid Term6.914
262nd Term13.714
27Final Term4.364
28Grand Total7.264
2nd
Cell Formulas
RangeFormula
A14,A24,A19A14=A9+1
AE9:AE28AE9=IF($B9="Grand Total",COUNTIFS($B$9:$B$9999,$B9,$AA$9:$AA$9999,">"&AA9)+1,COUNTIFS($C$9:$C$9999,$C9,$AA$9:$AA$9999,">"&$AA9)+1)
 
Upvote 0
Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Rank for different Criterion Groups
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 
Upvote 0

You can see the file here. as I think you can do it better with file
 
Upvote 0

You can see the file here. as I think you can do it better with file
I downloaded that file, corrected the last student name from "Grand Total" to "James" and the formula I posted in Post 12 works for me. Toadstool Post 12

If you are not getting the results you desire then please explain which are incorrect and what you would expect:
1640718606342.png
 
Upvote 0
Solution

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,569
Latest member
Honeymonster123

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