index? match? Rank? HELP!

[jbaich]

Hi all thanks for reading, Basically I've got the following


Col A-----------------Col B---------------------Col C--------------------Col D

Building ID-----------Count---------------------Rental Rate---------------Rank


in col A i have a list of building id's, including duplicates as each building may have one or more rental rates and each rental rate generates a record. I have used count in col B as i thought this might help. Col C contains the rental rates for each corresponding building ID and in Col D i want to rank the rental rates for each building separately.


The closest i've come (and i've tried several different trains of thought) is

=IF(B2=1,1,IF(B2=2,RANK(C2,$C2:$C3,0),IF(B2=3,RANK(C2,($C2:$C4),0),0)))


but as you can immediately see this fails when there are 2 or more occurances of a building id as the reference needs to apply to 2 or 3 rows, depending on how many occurances of the buidling id.


For example if i have...

Col A-------------------Col B---------------------Col C--------------------Col D

Building ID-------------Count---------------------Rental Rate---------------Rank


07644525------------------1---------------------------10----------------------1

07644675------------------1----------------------------7----------------------1

07755000------------------1---------------------------11.5--------------------1

08149000------------------1----------------------------5----------------------1

08457000------------------1---------------------------10----------------------1

09392000------------------1---------------------------10----------------------1

00802000------------------2---------------------------10----------------------1

00802000------------------2---------------------------9.5---------------------1

00842000------------------2---------------------------7-----------------------1

00842000------------------2---------------------------5-----------------------2

00843000------------------2---------------------------7-----------------------1

00843000------------------2---------------------------5-----------------------2

00877000------------------2---------------------------10----------------------1

00877000------------------2---------------------------9.5---------------------2

00884000------------------2---------------------------10----------------------1

00884000------------------2---------------------------9.5---------------------1

00889000------------------2---------------------------8.5---------------------1

00889000------------------2---------------------------8-----------------------2

05469000------------------3---------------------------6-----------------------1

05469000------------------3---------------------------5-----------------------2

05469000------------------3---------------------------1.4---------------------3

11162750------------------3---------------------------5.5---------------------1

11162750------------------3---------------------------3-----------------------2

11162750------------------3---------------------------2.75--------------------3

19000000------------------3---------------------------10----------------------1

19000000------------------3---------------------------9.5---------------------1

19000000------------------3---------------------------5-----------------------1


You can see that the rank does not work with multiple entries because the ref array in the rank ($C2:$C3,0),IF(B2=3,RANK(C2,($C2:$C4) would need to look like $C$2:C$3 for 2 occurances and $C$2:$C$4 for 3 occurances, however formatted like this obviously would apply only this specific ($C$2:$C$4) series of ref cells down the whole list...


I need it to be able to find and rank however many rental rates are associated with each bldg id... i don't know if Index and Match would do this? I think if there were such thing as a RANKPRODUCT like a sumproduct formula, that might work... any ideas?


Thanks,

Joe

 

[NARAYANK991]

Hi Joe ,


Can you try this and see ?


Create the following dynamic named ranges :


1. Named_Range , which refers to your data in column A , as follows :


=OFFSET($A$2,0,0,COUNTA($A:$A)-1)


2. List , which refers to a subset of the range of rates , where the building ID is the same , as follows :


=OFFSET($A$2,MATCH(A2,Named_Range,0)-1,2,COUNTIF(Named_Range,A2))


In cell D2 , have the formula =RANK(C2,List)


Copy this formula to all the remaining relevant cells in column D.


Narayan

 

[Luke M]

Thankfully, it looks like your list of building ID's is already sorted. With that condition, you can use this formula:

=RANK(C2,OFFSET(INDEX(C$2:$C$28,MATCH(A2,A$2:A$28,0)),0,0,B2,1))


You were on the right track, just needed to use OFFSET so that a range reference would be fed into the RANK function.

 

[3G]

Hey this is a cool trick!