Rank with multiple column and criteria

[yeo_1987]

Hi pals. I am creating an excel to rank the achievement of pupils. Please help me to write a formula to rank them according to multi column and criteria:

1. Rank those with 0 Grade E first, then only follow with those with >0 Grade E
2. For those with 0 Grade E, rank Grade A, follow by Grade B, C and D
3. For those with >0 Grade E, rank Grade A, follow by Grade B, C, D and E

Thanks in advanced.

 

[Rijnsent]

Hi Yeo,
what is the desired outcome, as in: can you show with some examples who would be first, second, third etc? A rather simple approach is calculate a score for every Name by simple multiplication, so e.g.
G2: =IF(F2>0,1000000;0)+B2*100000+C2*10000+D2*1000+E2*100+F2*10 -> that should give every person a value on which you could run a rank formula. This assumes that a higher score is better and that every score runs from 0-9.
Hope that helps,
Koen