AVGIF Formula Based on Date Range
Hello everyone,
I'm trying to get a average of a column if the cells in another column are within 90 days of today.
My current formula is =AVERAGEIF([Pick-up Variance]:[Pick-up Variance], [Pick-up Date]@row>TODAY(-90)).
I tried using AVG(Collect) as well with no luck.
=AVG(COLLECT([Pick-up Variance]:[Pick-up Variance], [Pick-up Date]:[Pick-up Date], , but I'm getting a Invalid Operation.'
Best Answer
-
@eLIZo That error message indicates an issue with the operator for the TODAY value. Let's try this, it seems to work in my test sheet:
=AVERAGEIF([Pick-up Date]:[Pick-up Date], @cell >TODAY(-90), [Pick-up Variance]:[Pick-up Variance])
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Answers
-
@eLIZo The syntax for the AVERAGEIF function is:
AVERAGEIF(range, criterion, [range to average])
So your formula should be something like:
=AVERAGEIF([Pick-up Date]:[Pick-up Date], >TODAY(-90), [Pick-up Variance]:[Pick-up Variance])
"Give me the average of the Pick-up Variance values in all the rows where the Pick-up Date is within the past 90 days."
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
-
Yeah, I created the formula that way first and received a INVALID OPERATION. Tried it again and same thing
-
@eLIZo That error message indicates an issue with the operator for the TODAY value. Let's try this, it seems to work in my test sheet:
=AVERAGEIF([Pick-up Date]:[Pick-up Date], @cell >TODAY(-90), [Pick-up Variance]:[Pick-up Variance])
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 406 Global Discussions
- 215 Industry Talk
- 456 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!