• 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

  • RentRoll.xlsx
    127.7 KB · Views: 3
Actually, the formula above are different cell references than what's in the excel attached..
 
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

  • Chandoo46945RentRoll.xlsx
    168.8 KB · Views: 4
Last edited:
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?
 
Yes, see attached. I'd like to be able to choose NEW leases or RENEWAL leases and see the most recent 'x' number of rents.. Designation of New or Renewal is in column AR156. Thank you for taking a look
 

Attachments

  • RentRoll(2) - recent leases - New or Renewals.xlsx
    452.1 KB · Views: 1
See formulae in AB11:AC11 copied down.
Occasionally you'll see #CALC! which is probably because there are none. Suggest you wrap the whole thing in IFERROR.
 

Attachments

  • Chandoo46945RentRoll(2) - recent leases - New or Renewals.xlsx
    455.7 KB · Views: 5
Back
Top