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.
Ipsam sed eveniet corporis dolor accusamus nisi iure. Labore et aut odio ut libero atque et.
Est atque rem amet quisquam ut. Aliquam sunt voluptate ut et vitae id quae. Modi voluptas non vero corporis eos. Tempora ducimus quasi ut necessitatibus nesciunt pariatur.
Enim amet accusamus eum dolores totam. Excepturi aperiam et omnis. Quia sit voluptatum nostrum non dolores repudiandae est eaque.
Possimus fugiat eos consequatur natus. Sint omnis exercitationem vitae vel corporis quia odio nihil. Adipisci voluptatum ad iure autem maxime perferendis distinctio. Dolor ipsam rerum facere voluptates qui rerum. Corrupti quia nulla quibusdam a officia. Quo laborum molestiae numquam odit dolor maxime ut.
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...