RANK based on result and average

[Narendra Boga]

Hi All,

I have a trouble with Rank() function. I have written normal rank function
based on "Average" column and working fine. But I need the rank based on the
"result" which ignore the averages of "fail" sting in Result column. I need
the result in "Rank" column as below:


Student name sub1 sub2 sub3 Result Average Rank
A 36 54 78 PASS 56.00 2
B 66 25 54 FAIL 48.33 N/A
C 45 36 65 PASS 48.67 3
D 12 45 45 FAIL 34.00 N/A
E 78 78 54 PASS 70.00 1

Here, the rank function ingnore the averages of fail students and rank will
calculate on only pass students.
Please suggest me in this regard.


Narendar

 

[Markytee]

There are probably better ways than this, but I'd create an additional column
(between Average and Rank on your example) which works on the basis that if
the result is "Pass", then return the average score, else return "", and run
the rank from that column. You could always hide that column for the
purposes of neatness.

 

[Jacob Skaria]

As the result is based on average marks..the below formula will work for the
example/scenario mentioned...

=IF(E2="Pass",RANK(F2,F$2:F$6,0),"N/A")

If this post helps click Yes

 

[Narendra Boga]

Dear Jacob,

Thank You for the formula. This formula is working for this example only.
I have given only some students data. In fact, I have to assign rank for
more than 100 students in each class. I need only one best formula that
change the rank when the marks change and as well as result.

 

[Narendra Boga]

Thank You Markytee... Good idea.

Cant we frame formula for this?

 

[Lars-Åke Aspelin]

You have made the assumption that the critiera for PASS or FAIL is
just the average. That might very well be true, but if the criteria
for PASS is "to have at least 30 on each of sub1, sub2, and sub3" and
then the average of a student who has FAILed does not necessarily be
lower than all students that have PASSed. In that case the proposed
formula will give the wrong result.

It is always difficult when the problem is not cleary stated.

Lars-Åke