[Solved] Rank formula

[vimal.ku2009]

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.

 

[Lutfor Rahman Shimanto]

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:

​

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

 

[vimal.ku2009]

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.

 

[Lutfor Rahman Shimanto]

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))​

​

OUTPUT:

​

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

​

Thank you again for reaching out to us.

Regards
Lutfor Rahman Shimanto