MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Rank IF


Posted by Richard Larocque on January 07, 2002 7:17 PM

Industry Ratings Expected Results
a 9 1
a 7 2
a 5 3
b 3 3
b 1 4
b 8 1
b 6 2
c 4 1
c 2 2
d 3 1

I'm looking to have a sort of Rank(If) formula. With every change in industry, the ranking system is applied to that particular industry.


Posted by Richard Larocque on January 07, 2002 10:15 PM


Let me clarify. With each change in column A (Industry), the values in column B (Ratings)are ranked in column C.

Thanks

Posted by Aladin Akyurek on January 08, 2002 3:53 AM

Richard --

In C2 array-enter: =MATCH(B2,LARGE(IF($A$2:$A$11=A2,$B$2:$B$11),ROW(INDIRECT("1:"&COUNTIF($A$2:$A$11,A2)))),0)[ copy down this as far as needed ]

In order to array-enter a formula, hit CONTROL+SHIFT+ENTER at the same time, not just ENTER.

PS. I'm indebted to Joel Horowitz who posted a similar but complicated question, allowing design an appropriate formula for RankIF.

Aladin

Posted by Richard Larocque on January 08, 2002 7:03 AM

Once again. Thanks so much Aladin. I'm now calling my six-year old girl Aladin!

Posted by Richard Larocque on January 08, 2002 7:05 AM

Once again. Thank you so much!