I am having a problem with the returning a value part of this formula.
My data is set up like so (dashes included to make it appear like columns in the post):
RR.UnitType----RR.NetContractedRent----RR.LeaseStart
a--------------100----------------------1/1/13
b--------------200----------------------1/2/13
c--------------300----------------------1/3/13
a--------------400----------------------1/4/13
b--------------500----------------------1/5/13
c--------------600----------------------1/6/13
So far I have the formula to pull back the last 5 dates for each unit type (this is the partially solved part):
{=LARGE(IF(RR.UnitType=$A$4,RR.LeaseStart),5)} <-this being the 5th latest date
$A$4 = the unit type I want to retrieve
Now my problem is finding the Net Contracted Rent that corresponds to that specific unit type and date. It should be noted that there could be multiple unit type "A"s with the same lease start date.
Any ideas out there?
Thank you in advance.
Bookmarks