7 Day Average/30 Day Average
I am trying to build a formula for a data set that I can calculate as a complete average, but I'd like the 7 day average and the 30 day average.
Here's the formula that works: =AVG([% Successful]:[% Successful])
I know I will need to use AVERAGEIF, but can not get the date parts correct.
Thanks for your help!
Best Answer
-
Hi @Amy Knoll
Since you have a date range (ex. from a certain date to a certain date), we can actually use an AVG(COLLECT formula to specify more than one criteria.
Try something like this:
=AVG(COLLECT([% Successful]:[% Successful], [Date Column]:[Date Column], <= TODAY(), [Date Column]:[Date Column], >= TODAY(-7)))
This looks for a date that's less than or equal to Today, but that is greater than or equal to 7 days ago. For your 30 day average, you can swap out the -7 to be -30.
If you're getting a divide by 0 error, this means that the answer is 0. You can wrap an IFERROR Statement around it to return 0 instead:
=IFERROR(AVG(COLLECT([% Successful]:[% Successful], [Date Column]:[Date Column], <= TODAY(), [Date Column]:[Date Column], >= TODAY(-7))), 0)
Is this the average you were looking for? If you want to specify an exact date instead of TODAY you can use the DATE function, like so:
[Date Column]:[Date Column], <= DATE(yyyy, mm, dd)... etc
Cheers!
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
Hi @Amy Knoll
Since you have a date range (ex. from a certain date to a certain date), we can actually use an AVG(COLLECT formula to specify more than one criteria.
Try something like this:
=AVG(COLLECT([% Successful]:[% Successful], [Date Column]:[Date Column], <= TODAY(), [Date Column]:[Date Column], >= TODAY(-7)))
This looks for a date that's less than or equal to Today, but that is greater than or equal to 7 days ago. For your 30 day average, you can swap out the -7 to be -30.
If you're getting a divide by 0 error, this means that the answer is 0. You can wrap an IFERROR Statement around it to return 0 instead:
=IFERROR(AVG(COLLECT([% Successful]:[% Successful], [Date Column]:[Date Column], <= TODAY(), [Date Column]:[Date Column], >= TODAY(-7))), 0)
Is this the average you were looking for? If you want to specify an exact date instead of TODAY you can use the DATE function, like so:
[Date Column]:[Date Column], <= DATE(yyyy, mm, dd)... etc
Cheers!
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
This worked PERFECTLY. Thanks so much for the help!!
-
I'm so glad! Thanks for letting me know. 🙂
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!