MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Ranking


Posted by lars on June 11, 2001 1:42 PM

How do I rank the following with a formula? I need a formula in column c (Below -Cities are in column A and the Fares are in Column B)ranking the fares from lowest to highest (lowest is 1) for each city group. Column C is what I want to see. Hope this makes sense...Please help

ABE 500 3
ABE 400 1
ABE 450 2
DFW 375 2
DFW 350 1
LAX 480 1
LAX 500 2


Posted by IML on June 11, 2001 2:52 PM


I think I have one that should work for you. This need the list to be sorted by airport and assumes your list is in A1:B8. Good luck.

=RANK(B1,INDIRECT(ADDRESS(MATCH(A1,$A$1:$A$8)-COUNTIF($A$1:$A$8,A1)+1,2)):INDIRECT(ADDRESS(MATCH(A1,$A$1:$A$8),2)),1)


Posted by Mark W. on June 11, 2001 3:08 PM

With your data entered into cell A1:B7, enter the
following array formula into cell C1 and Copy down:

{=SUM((A1=$A$1:$A$7)*(B1>=$B$1:$B$7))}

Note: array formulas must be entered using the
Control+Shift+Enter key combination. The braces,
{}, are not entered by you.

Posted by Mark W. on June 11, 2001 3:10 PM

Please note...

...that no special ordering of your data is required.

Posted by IML on June 11, 2001 3:14 PM

A word of caution about this formula...

Please beware that this much shorter and much simplier than mine...Nice work, Mark.

Posted by Mark W. on June 11, 2001 3:31 PM

Re: A word of caution about this formula...

There is one itsy-bitsy flaw that I'm still trying to
overcome! Perhaps, you'd like to assist. If there's
a tie RANK() will assign the lower rank to both
elements; however, {=SUM((A1=$A$1:$A$7)*(B1>=$B$1:$B$7))}
will apply the higher rank. For example...

ABE 500
ABE 400
ABE 400

will be ranked as {3;2;2} when it should be {3;1;1}.

Posted by Mark W. on June 11, 2001 3:41 PM

Okay, this one addresses my last concern...

{=SUM((A1=$A$1:$A$7)*(B1>=$B$1:$B$7))-SUM((A1=$A$1:$A$7)*(B1=$B$1:$B$7))+1} There is one itsy-bitsy flaw that I'm still trying to will be ranked as {3;2;2} when it should be {3;1;1}.

Posted by Mark W. on June 11, 2001 3:57 PM

Now, for a simplification... That's it... Done! : )

{=SUM((A1=$A$1:$A$7)*((B1>=$B$1:$B$7)-(B1=$B$1:$B$7)))+1} {=SUM((A1=$A$1:$A$7)*(B1>=$B$1:$B$7))-SUM((A1=$A$1:$A$7)*(B1=$B$1:$B$7))+1} : There is one itsy-bitsy flaw that I'm still trying to : will be ranked as {3;2;2} when it should be {3;1;1}. :

Posted by Mark W. on June 11, 2001 4:19 PM

Nope, I lied... This is the last one : )

{=SUM((A1=$A$1:$A$7)*((B1>$B$1:$B$7)))+1}

Good night! {=SUM((A1=$A$1:$A$7)*((B1>=$B$1:$B$7)-(B1=$B$1:$B$7)))+1} : {=SUM((A1=$A$1:$A$7)*(B1>=$B$1:$B$7))-SUM((A1=$A$1:$A$7)*(B1=$B$1:$B$7))+1}

Posted by Lars on June 11, 2001 4:25 PM

Regarding your concern

Great work it works but as you said if there is a tie it will come back as (3;2;2) but should be (2;1;1) not (3;1;1) I can live with that.

Thanks!!!!

{=SUM((A1=$A$1:$A$7)*(B1>=$B$1:$B$7))-SUM((A1=$A$1:$A$7)*(B1=$B$1:$B$7))+1} : There is one itsy-bitsy flaw that I'm still trying to : will be ranked as {3;2;2} when it should be {3;1;1}. :

Posted by lars on June 11, 2001 4:46 PM

Thanks for the help great work Mark!!!!!

Good night! : {=SUM((A1=$A$1:$A$7)*((B1>=$B$1:$B$7)-(B1=$B$1:$B$7)))+1}

Posted by BJ on June 12, 2001 3:44 AM


IML... can you look at my quesion Re: Find Function? much appreciated....
BJ

Posted by Mark W. on June 12, 2001 6:36 AM

Re: Regarding your concern

{3;1;1} is what Excel's RANK() function will do.
And it makes a great deal of sense! There's a
tie for first, and the 3rd value can't be a 2nd
place "finisher" because there are 3 values. Great work it works but as you said if there is a tie it will come back as (3;2;2) but should be (2;1;1) not (3;1;1) I can live with that. Thanks!!!!

Posted by IML on June 12, 2001 7:52 AM

BJ-
Sorry, I don't a thing about VBA. I'm sure someone will be able to help you out though. I'm responsding here so it doesn't look like you got an answer to your post.

Good luck.