Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

Automate an averaging formula by date

Copper Contributor

Hello, I am running into an issue with a spreadsheet I am currently working on. I have data from the last 30 days and need to figure out how to insert the date into the function without manually doing it every time. The function I am currently using looks like this: 

 

=AVERAGEIFS(AVERAGE RANGE, CRITERIA RANGE1, ">=" & DATE(YEAR,MONTH,DAY),CRITERIA RANGE2, "<=" & DATE(YEAR,MONTH,DAY))

 

EX:

=AVERAGEIFS(F:F,E:E, ">=" & DATE(2023,03,30),E:E, "<=" & DATE(2023,03,30))

 

 

I am sure there is a more simple way to do this. If you need more info let me know. Thank you all!

2 Replies
best response confirmed by jaredcott6 (Copper Contributor)
Solution

@jaredcott6 

If you want to average over the last 30 days, use

 

=AVERAGEIFS(average_range, date_range, ">"&TODAY()-30, date_range, "<="&TODAY())

Thank you! That worked perfectly
1 best response

Accepted Solutions
best response confirmed by jaredcott6 (Copper Contributor)
Solution

@jaredcott6 

If you want to average over the last 30 days, use

 

=AVERAGEIFS(average_range, date_range, ">"&TODAY()-30, date_range, "<="&TODAY())

View solution in original post