How to make a Mark Sheet for Students using Excel ?

[anburajabe]

Sir

I submit my file (Sheet).


I complete all my data entry work in this file. Finished. All are Correct.

I calculate RANK also. i am using this formula

IF(K2="PASS",RANK(J2,J$2:J$30),"")

But one mistake in it. Normally J17 will get the Second Rank.
But it shows third Rank.

Because J25 has highest TOTAL compared to J17. But K25 has FAIL.

So i want a FORMULA to correct this.