RANK based on result and average

N

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
 
M

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.
 
J

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
 
N

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.
 
L

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top