Need help with a formula
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
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.
Illo necessitatibus modi commodi molestias qui dicta quis consectetur. Et labore totam labore ut ut architecto omnis accusantium.
Ut quas sit et vel. Ut in praesentium explicabo ut deserunt. Est doloribus beatae est atque eos non. Quas perspiciatis voluptate commodi cumque amet placeat cum quis.
Vitae ullam repellat cumque facere. Culpa voluptatum provident placeat perspiciatis laudantium qui error. Consequuntur voluptatem exercitationem dolor nostrum velit placeat provident.
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...