Archive of Mr Excel Message Board


Back to Forms in Excel VBA archive index
Back to archive home

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


Check out our Excel Resources

Re: Rank, Countif

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

or

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

Aladin

=======


Re: Rank, Countif

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


Re: Rank, Countif

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!

--


Re: Rank, Countif

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

=====


Re: Rank, Countif

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


Re: Rank, Countif

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! --


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.