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

On Sun, 22 Nov 2009 00:43:02 -0800, Jacob Skaria
wrote:

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
---------------
Jacob Skaria


"Narendra Boga" wrote:

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