Hi all thanks for reading, i have attached a sample of the data i am working with... 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 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
Bookmarks