[Solved] Rank formula

vimal.ku2009

New member
Hello,
I was trying to find the rank of individuals but there are some students who are absent in some tests. I have to find the rank of other students excluding those who are absent. They should be shown as "FAIL".while running the RANK function it is taking consideration of the failed students also.

Please solve the issue. Taking into consideration that no more columns should be added. Rank should be calculated in Rank column only.

Please find the attachment.
 

Attachments

  • MSExcel Practical.xlsx
    10.8 KB · Views: 3
Hello,
I was trying to find the rank of individuals but there are some students who are absent in some tests. I have to find the rank of other students excluding those who are absent. They should be shown as "FAIL".while running the RANK function it is taking consideration of the failed students also.

Please solve the issue. Taking into consideration that no more columns should be added. Rank should be calculated in Rank column only.

Please find the attachment.
Hello Vimal.ku2009

Thanks for reaching out and clarifying your requirements. Regarding your issue, I understand that you want to calculate the rank of students while excluding the absent students. To achieve this, you can use the following formula:

=IF(COUNTIF(C4:G4,"Absent")>0,"FAIL.",RANK(I4,$I$4:$I$10,0))

OUTPUT:
Vimal.ku2009.png

I am attaching the Workbook used to explore your issue. Don't hesitate to contact us if you face any other issues.

Regards
Lutfor Rahman Shimanto
 

Attachments

  • Vimal.ku2009.xlsx
    11.2 KB · Views: 3
Brother it's same as i have calculated. Failed persons shouldn't be included while ranking. Rank should be assigned to the students who have given all the papers.
 
Brother it's same as i have calculated. Failed persons shouldn't be included while ranking. Rank should be assigned to the students who have given all the papers.
Dear Vimal.ku2009

Thank you for explaining your problem with such clarity. I have successfully solved the issue you faced by using a Helper Column. Later, I did hide that column for the sheet to be visually better. I'm attaching the Workbook used to look into your problem. I have developed two formulas to resolve your issue.

HELPER COLUMN:
=IF(COUNTIF(C4:G4,"Absent")>0,0,AVERAGEA(C4:G4))

Vimal.ku2009(HelperColumn).png

OUTPUT:
=IF(COUNTIF(C4:G4,"Absent")>0,"FAIL.",RANK(K4,$K$4:$K$10,0))

Vimal.ku2009(OUTPUT).png

Thank you again for reaching out to us.

Regards
Lutfor Rahman Shimanto
 

Attachments

  • Vimal.ku2009.xlsx
    11.2 KB · Views: 1

Online statistics

Members online
0
Guests online
9
Total visitors
9

Forum statistics

Threads
287
Messages
1,240
Members
508
Latest member
HaroldDyeme
Top