• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Find the 'x' most recent rents of a set of renters based on floor plan ID

djk

New Member
Hi, I want to find the most recent leases (1,2,3,etc) based on the floor plan ID of a list of units at an apartment community. I tried using the OFFSET and MATCH functions like this but only works if the data is sorted by Floor Plan (which I don't want to do - the data is already sorted by most recent leases first and would like to keep that): =SUM(OFFSET($AD$156,MATCH($D11,$V$156:$V$2655,0)-1,0,$AA$9))/COUNT(OFFSET($AD$156,MATCH($D11,$V$156:$V$2655,0)-1,0,$AA$9))

See attached file.
Thanks!
 

Attachments

djk

New Member
Actually, the formula above are different cell references than what's in the excel attached..
 

p45cal

Well-Known Member
See cell S9 in sheet Floor Plan and copy down.
There's a cross check in the pivot table at cell U8, although you'll need manually to adjust the value filter|top 10… in the UnitID dropdown (cell V8) to match what's in cell S7.
This does NOT depend on the sort order of RentRoll table (as you might be able to glean from the SORTBY element of the formula).

If you don't have these newer worksheet functions then in a cell in row 9 you can have:
Code:
=AVERAGEIFS(CurrentInPlaceRent,FloorPlan,FloorPlans[@PlanID],CurrentLeaseSignedDate,">=" & AGGREGATE(14,2,IF(FloorPlan=FloorPlans[@PlanID],CurrentLeaseSignedDate),MIN($S$7,COUNTIF(FloorPlan,FloorPlans[@PlanID]))))
which may need to be entered with Ctrl+Shift+Enter (array-entered) rather than plain Enter.
 

Attachments

Last edited:

djk

New Member
This works really well thank you p45cal!!
One more question: I have determined if a lease is a NEW or a RENEWAL (meaning the resident either just moved in or they have lived there awhile). So I have another column that lists NEW or RENEWAL. How can I add this to either the Sort or the Sequence part of your formula?
 
Top