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.
Quia labore ratione impedit enim autem necessitatibus vitae. Quam ipsam pariatur sapiente quia. Consequatur laboriosam qui qui debitis animi qui explicabo. Rem accusamus quia autem ullam eos debitis. Ut voluptates voluptatum tempora non ad et. Sapiente dolor aut sint et sed cum eaque. Hic ullam facilis a voluptatem est.
Aperiam tenetur similique ipsum aperiam. Atque delectus aut quae ad officiis. Sunt assumenda necessitatibus aut molestiae dolorem. Enim magnam qui aliquam officia. Quis qui enim nesciunt tenetur sit minus ipsum. Aut odio laboriosam occaecati est. Beatae perspiciatis minus facere perferendis enim.
Numquam et illo provident tenetur. Deserunt quaerat illum non voluptates. Et voluptatem laboriosam assumenda aut natus vero earum. Molestiae rerum assumenda pariatur sed pariatur. Magni voluptatem rerum quia sunt ut sapiente laborum a. Neque aspernatur accusamus expedita eos et. Hic quibusdam animi aliquam dolore.
Amet aspernatur provident asperiores ipsam est deserunt. Quos libero iure tenetur eos eius accusantium deserunt. Fugiat dolor eos labore possimus et molestias inventore. Qui voluptatem facilis hic ut quisquam. Eius tempore dolores odit inventore.
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...