Average IF Within 30 days
Hello,
I am trying to figure out a formula to average a particular column of information, but only within the last 30 days. I have a "Todays Date" Formula/column already created, but not sure how to write the formula to be able to read my whole column of data but only pull from the past 30 days.
I appreciate any help in advance.
Thank you,
Best Answer
-
Try something similar to...
=AVG(COLLECT([Column to Average]:[Column to Average], [Date Column]:[Date Column], AND(@cell >= TODAY(-30), @cell <= TODAY()))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
Try something similar to...
=AVG(COLLECT([Column to Average]:[Column to Average], [Date Column]:[Date Column], AND(@cell >= TODAY(-30), @cell <= TODAY()))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
That worked perfectly. Thank you Paul!
-
Happy to help! 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
@Paul Newcome trying to do something similar here. I am tracking go live dates, total implementation days. I want to average the total days to implement of those with a go live date in the last 90 days only. I tried: =AVG(COLLECT([Est Total Imp Days]:[Est Total Imp Days], [Go-Live Date (Actual)]:[Go-Live Date (Actual)], >TODAY(), [Go-Live Date (Actual)]:[Go-Live Date (Actual)], <=TODAY())) but am returning #DDIVIDE BY ZERO error when there are definitely go live dates within the past 90 days.
-
@jcouncil Your criteria for the date range would be
@cell>= TODAY(-90)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
@Paul Newcome - I was hoping to attempt this on the Sheet Summary, would that still work?
-
@jcouncil Yes. It should still work in a sheet summary field.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!