All -
I'm working on valuing/developing a model for institutional-sized multi-family deals. I need to create a formula that pulls the average rent for the 5 most recent leases signed for that given floorplan.
For example for 1BD/1BAv1 I need to find those floorplans and then find the 5 most recent dates and then average those values. I've tried some SUMIF & AVERAGEIF formulas but have been held up on the second criteria identifying the 5 most recent dates. Anyone else run into this problem or have a fix?? Thanks!
Rank the lease date by floorplan using COUNTIFS. Then you do average of 1-5 using SUMIF.
Curious, are you trying to sort the rent roll for this so you can see where the newest leases were signed? And than modeling rents based on that?
See below google drive link for the formula I drew up in excel..let me know if this is what you were looking for?
https://drive.google.com/file/d/1A3S71Aw3ox_MPkX7FuY1t792b8asM3RW/view?…
Yeah, this is super helpful, do you think there's a way to do it without the ranking column? ie consolidate it all into one formula??
On the column with the lease dates, use the large function in excel large([date column], 5) will give you the 5th most recent date, then use the averageifs likes this averageifs([average range], [date range], “>=“&large([date range], 5))
What about factoring in the specific floorplans? When I calculate the fifth most recent date, is there a way to do that per floorplan?
There is with array formulas, but it’s unwieldy. You are better off adding a column for the how recent the lease is. Then use countifs in that new column like this:
countifs([floor plan column], cell with that rows floor plan, [date column], “>=“&that rows date cell)
then average if where that new column is less than or equal to 5.
Aut velit voluptatem ipsa perferendis. Officiis ducimus velit facilis. Provident ratione impedit et quia sint rerum.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...