ROLLING 30 DAY AVERAGE SHEET SUMMARY
Hello,
I'm looking to build a formula in the Sheet Summary that will provide a rolling 30 day average from the PROD values. (attached is a data subset) Feels like this should be fairly basic, but am struggling to find the solution. Anyone's help would be appreciated.
Answers

Try this:
=AVG(COLLECT(Prod:Prod, Date:Date, >=TODAY(30), Date:Date, <=TODAY()))
This will take the average of the first range (Prod:Prod), but only if the other column meets the criteria specified, which is why I used the COLLECT function.
The other criteria is that the Date is either greaterthanorequalto (>=) 30 days ago (Today(30)), OR that the Date is equal to today, or less (Today()).
Let me know if it works for you!
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
HI Genevieve,
Thank you so much for helping. Your assessment is exactly what I'm looking to do.
When I input your equation, i get a "#DIVIDE BY ZERO" result. Any thoughts as to why?
Appreciate the support!

I just realized you have commas in with the data you would like to AVG. This indicates that the numbers are actually listed as text, not as numerical data that is able to be averaged. How is that information being input? Is this from a formula or a manual entry?
If it's a formula, you could build in a VALUE function to ensure it's outputting a number instead of text... or what might be easiest is creating a helper column that simply has the formula:
=VALUE(PROD@row)
This will then create numbers and you can use this helper column as the first range in your AVG formula.
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
I have the "Thousands format" turned on to this column which adds the comma  visual preference. I tried the calc without the commas, but still get the #DIVIDE BY ZERO result.

=averageif(date:date,>=today(30),prod:prod) is a simpler way to do this.
Check the dates. Depending on how you input them it might be 1920 instead of 2020 for the year.

Is it an issue that I have each date represented twice, once for a day shift and another for a night shift?

@Steve Erdahl my best guess on this is that your PROD column has text values and not numerical values, going back to what @Genevieve P mentioned.
The reason this is likely, is that using the AVG function against text values will always return a #DIVIDE BY ZERO result. Creating a helper column and using =VALUE(PROD@row), then calculating using that helper column's data instead of your PROD column should do the trick!
Thanks,
Alex

Can this solution be used with avgif formula and rolling by quarter? Or would I just do =today (90)?

You would want to use AVG(COLLECT instead of AVGIF since AVGIF can only identify one criteria, but in AVG(COLLECT you can specify a range, or two criteria.
Yes, you can use =TODAY(90) for a rolling quarter!
Did you manage to get this to work?
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
Is there a way to only pull positive numbers from the data set for the average?
thanks

Hi @Matt N
Yes! In the COLLECT function you can reference a range and then identify the criteria as being "greater than 0". For example:
=AVG(COLLECT({Range to Average}, {Range to Average}, > 0))
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
I have tried to place both the greater than zero AND rolling 90day conditions in the formula and I get the #INVALID OPERATON result. Is there an order it has to come in? For example, do I need to set the greater than 0 condition first then the date condition or viceversa or does it not matter?

Hi @Matt N
The location of the {range} and "criteria" pair can be anywhere in the COLLECT function after the first range has been listed:
=AVG(COLLECT(range to avg, range1, criteria1, range2, criteria2))
In your test, it looks like you may have listed the [range] twice (before and after the criteria) instead of just once (before the criteria).
It happens to be the same column you want to AVG, so we'll keep the listing there at the front as the first one. Then you can go ahead to list your criteria pairs, does that make sense?
=AVG(COLLECT(Buffer Column, Buffer Column, greater than 0, Date, date criteria, Date, date criteria))
The bolded sections can be rearranged, as long as the pairs are together. So this still works:
=AVG(COLLECT(Buffer Column, Date, date criteria, Date, date criteria, Buffer Column, greater than 0))
Try:
=AVG(COLLECT([BUFFER OWREN VERONAL *17894 Usage (w)]:[BUFFER OWREN VERONAL *17894 Usage (w)], [BUFFER OWREN VERONAL *17894 Usage (w)]:[BUFFER OWREN VERONAL *17894 Usage (w)], > 0, DATE:DATE, >=TODAY(90), DATE:DATE, <=TODAY()))
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
That Worked! You're the best! Thanks!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.8K Get Help
 376 Global Discussions
 207 Industry Talk
 440 Announcements
 4.5K Ideas & Feature Requests
 139 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 284 Events
 33 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!