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.
Quia ipsam et voluptas quisquam quis dolore. Expedita nulla dolor aut.
Eaque qui repellat adipisci. Fugiat vero doloribus perferendis sed dignissimos quia ut. Sint voluptatum fuga sed expedita. Voluptatem et consequuntur recusandae animi et. Aut debitis et ipsa cum autem.
Deleniti exercitationem vero et eos debitis alias. Nisi et rem officiis accusantium minima voluptatibus consequatur.
Error nesciunt aut ea cum voluptatum placeat sed repellat. Sed magnam commodi eum perferendis esse. Placeat laborum id quia quis quia. Illo ipsam cumque eaque fuga. Tempora ut quo voluptate delectus. Sint dolorum quidem quaerat molestiae accusamus cupiditate. Voluptatem similique at molestias at voluptatibus et.
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...