conditional Ranking???

[richphi37]

Hey there gurus,

I want to rank my students. The ones who pass all of their tests on the first time rank higher than those who do not. I need to know how to rank the ones who do not pass after the ones who do. There is also an achievement based on GPA in the class (Column A in my sheet). Top 20% who pass all their tests first time get Commandants list, Top 40% who pass first time gets Academic Achievement. I can set it up so the top 20 and 40% GPAs are recognized and those who do not pass are left blank in Column A, but how do I rank the passers above those who did not pass though they have a higher final GPA.
I need to rank the passers over the non-passers in columns A and E.

The picture is only a screenshot. There are 45 students. so it should be 9 commandants list and 9 academic achievement. the rest in column A will should be blank..

The formula I currently use in Column A is:
=IF(C2="N","",IF(D2>=PERCENTILE(D2:D46,0.8),"Commandant's List",IF(AND(D2>=PERCENTILE(D2:D46,0.6),D2<>PERCENTILE(D2:D46,0.8)),"Superior Academic Achievement","")))

The formula I currently use in Column E is:
=RANK(D2,D2:D46)

No other formulas used here.
Any assistance would be greatly appreciated.

-Phil

 

[jasonb75]

It would be easiest with a (hidden if desired) helper column. Assuming column F is currently empty, enter the formula below into F2 then fill down and rank using the helper column.

=D2+(C2="Y")

Using this will give those that passed first time a virtual result 100% higher than their actual result. This will only apply to the hidden column, the original results will be unchanged.