Rank with multiple reference ranges

[blcexcel]

I'm being needy today. I have another question on the same spreadsheet I'm working on. I want to calculate Rank but I want it to calculate separately for each Sub. On my example file I have the reference range for the entire data table which I need but somehow I need to add a piece that takes the Sub into so there will be a 1-10 for Sub 215, a 1-10 for 320, and a 1-10 for 440. I did try searching on the web for an answer but couldn't find anything on this. Thanks in advance!

 

[eibi]

In cell A5:

=RANK.AVG(C5,OFFSET($C$4,MATCH(B5,$B$5:$B$34,0),,COUNTIF($B$5:$B$34,B5)))

drag to fill

Just for fun...I think this shorter array formula will work too??

=MATCH(C5,LARGE(N(($B$5:$B$34=B5)*($C$5:$C$34)),ROW($B$1:$B$10)),-1)

 

[blcexcel]

In cell A5:

=RANK.AVG(C5,OFFSET($C$4,MATCH(B5,$B$5:$B$34,0),,COUNTIF($B$5:$B$34,B5)))

I used the Rank formula and that did the trick! Thank you!

 

[bines53]

Hi blcexcel,

If always equal distribution of 10 lines , here's the solution, without CSE,

=MATCH(C5,INDEX(AGGREGATE(14,6,($C$5:$C$34)/($B$5:$B$34=B5),ROW($A$1:$A$10)),0),0)

David
http://chandoo.org/forum/members/blcexcel.5330/