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
| +57 | What's an Undergrad to Do? | 21 | 18h |
| +45 | Development Fees > Sponsor Equity Contribution | 9 | 2m |
| +38 | Advice to Job Seekers: Don't Sleep on Asset Management | 12 | 32m |
| +25 | Underwriting Predevelopment Costs | 5 | 5d |
| +19 | Job after RE Masters Degree \ Seeking advice | 8 | 3d |
| +17 | Automated Real Estate Modelling | 6 | 1h |
| +10 | Full-Time RE Acquisitions Analyst Recruiting - Class of 2028 | 4 | 1h |
| +7 | London REPE Comp | 6 | 2d |
| +5 | Wells Fargo Analyst opportunities | 13 | 4d |
| +3 | Tishman Speyer Debt Fund | 0 | 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.
Iure praesentium harum culpa ut pariatur. Odio consequuntur ratione libero tempore. Illum voluptatem delectus eos modi facere aliquam maxime veniam.
Culpa non aut dolor sequi veritatis a. Animi quaerat repellat et sit. Mollitia omnis pariatur tempora in. Fugiat iste similique vel. Voluptatibus consequatur excepturi neque doloremque dolor. Dolores rem perferendis harum dolore. Illum possimus rem mollitia. Nesciunt consequuntur dignissimos velit voluptas.
Non aliquid et sint voluptates fuga beatae. Ad ut molestiae commodi error. Rem assumenda harum animi libero. Corrupti reprehenderit nulla est blanditiis. Voluptatem exercitationem eos accusamus aspernatur occaecati eos.
Qui cumque eligendi mollitia corrupti consequatur dolorum. Necessitatibus debitis nostrum voluptatem consequatur ex quia omnis. Praesentium minima mollitia fuga soluta nemo quisquam qui.
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...