Rank, Countif

Posted by Bruce on January 07, 2002 10:57 AM

I have a class up to 200 students and trying to break ties of class ranking based on academic scores:
A Column: Class ranking (Formula =RANK(G7,G$7:G$200)

G Column: Academic scores (Formula =+(C10*0.1)+(D10*0.4)+(E10*0.1)+(F10*0.4)

I end up with ties in ranking due to same test scores, so I want to go to the M Column which has overall scores for the tie breaker

M Column: Overall scores (Formula =AVERAGE(G7:L7) I tried adding this formula to M Column but changed the overall average, and did not affect the rankings in A Column as desired. =Average(G7:L7)+COUNTIF(G$7:G7,G7)-1

Thanks in advance for you input!

Bruce

Posted by Aladin Akyurek on January 07, 2002 11:17 AM

Bruce --

I seem to have a deja vu here.

But try either

=RANK(G7,G$7:G$200)+COUNTIF(G$7:G7,G7)-1

=RANK(M7,B$7:B$200)+COUNTIF(M$7:M7,M7)-1

Aladin

=======

Posted by Bruce on January 07, 2002 12:53 PM

Posted by Bruce on January 07, 2002 1:03 PM

Thanks! The formula =Rank(G7,G$7:G$200)+Countif(G$7:G7,G7)-1, worked except for one minor problem. It broke the tie but if two people were tied in academics say each with a score with 89, and both tied at 9, the one with the highest overall score should have the lowest ranking. It did just the oposite, and the one with the lower overall score got the lower ranking.

Thanks for your patience!

Posted by Aladin Akyurek on January 07, 2002 1:32 PM

Bruce --

Not sure but maybe you're looking for:

=RANK(G7,G$7:G$200,1)+COUNTIF(G$7:G7,G7)-1

which rank the numbers in ascending order.

If this does not help, please post 10 rows of relevant data with expected results.

Aladin

=====

Posted by Joel Horowitz on January 07, 2002 3:22 PM

I think I have a solution that is not very Excel-ish, and a little quick and dirty:

instead of ranking on column G, create a new column N7=G7+average(G7:L7)/10^10, and rank the student based on column N.

Joel

Posted by Bruce on January 08, 2002 7:14 AM

Any chance you would give me an e-mail address and let me send you a copy of my Excell work sheet? If so just respond to my e-mail address.

Thank you for your input! --