Hi. I want to enter a formula that will return the average rent of all units leased within the last 3 months from latest lease and another that will return the average rent of all leases before those 3 monthsper unit type. Any advice? Thanks
| +24 | Hotel Recruiters | 14 | 1d |
| +15 | Senior Living Modeling Help | 5 | 2h |
| +12 | Leaving Self-employment for Commercial Real Estate Brokerage | 2 | 2d |
| +9 | REPE Analyst Firm Advice | 1 | 10h |
| +7 | KeyBank RE Underwriting | 6 | 2d |
| +6 | Rate Hikes | 2 | 23m |
| +3 | What are the best undergrad schools for placing in real estate finance? | 6 | 16h |
| +3 | BGO REPE LDN | 0 | 3h |
| +3 | Overtime approval | 5 | 19h |
| +2 | Anyone familiar with Migsun Nehru Place, Greater Noida? Thoughts on investing here? | 3 | 3d |
Career Resources
Get instant access to lessons taught by experienced private equity pros and bulge bracket investment bankers including financial statement modeling, DCF, M&A, LBO, Comps and Excel Modeling.
There's probably more than 1 way to do it but here's my first thoughts:
- Use a Max function on the dates of the leased units and this will find the last leased unit
- In another cell you can use ="whatever cell you used for the max" - date(0,3,0) to get 3 months prior
-then you can do an averageifs function and have the criteria be greater than or equal to the lower end of that range and less than or equal to the upper end of that range and have the averaged range be the rent
Hard to know without seeing your spreadsheet but do either of the below work?
=AVERAGEIF()
=AVERAGEIFS()
From Chat GPT:
"Assuming that the data is stored in columns "A" through "C" with "A" being the date of lease, "B" being the unit number, and "C" being the rent amount, the formula to calculate the average rent of all units leased in the last three months would be:
=AVERAGEIFS(C:C,A:A,">" & MAX(A:A) - 90,A:A,"=" & MAX(A:A))
This formula uses the AVERAGEIFS function to calculate the average of all rent amounts (column C) where the date of lease (column A) is greater than the maximum date of lease minus 90 days, and the date of lease is less than or equal to the maximum date of lease."
rip analysts
Is this actually what you guys would do?
What's the question?
Generally, yes. For the average rent of all units in the last 90 days, I'd use the =AVERAGEIF function to average the rent column if the date column is less than today's date minus 90 days. For the average rent for last 90 days for each unit type, I'd use the =AVERAGEIFS function to do the exact same as previously mentioned, but set up a table to include a separate logical function to determine if the the unit type from the raw data is equal to the table just set up.
Ipsam tempore itaque non saepe. Nobis doloremque error quia quaerat. Hic aut sed distinctio nobis ut quisquam esse exercitationem. Sed hic officiis consequuntur fuga quisquam. Laboriosam sit quisquam natus rerum. Repellendus odio et molestiae accusantium et at 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...