Asking for AVG if specific date criteria is meet.
Asking to average the number of days worked on a project if the due date was after 2020, 12, 31.
Something in this is not working correctly.
=AVG(COLLECT({Days Total}:{Days Total}), IF({Completion Date}, >DATE(2020, 12, 31)))
Answers
-
Hi @Tbech
From your use of curly brackets {} I see you have a cross sheet reference. When using cross sheet references, we don't use the colons like we would if we were referencing columns on the same sheet.
Since you only have a single criteria to evaluate, you have two options of formulas. I'll show you the AVG(COLLECT) first since you started with that (always my preference as well)
=AVG(COLLECT({Days Total}, {Completion Date}, >DATE(2020, 12, 31), {Completion Date}, ISDATE(@cell)))
I added the ISDATE criteria to weed out any date errors that sometimes occur.
Smartsheet does have an AVERAGEIF formula that can be used when you have a single criteria, like you do above.
AVERAGEIF has the syntax of criteria range, criteria, [Average range (if different than the criteria range)]
Applying this to your example:
=AVERAGEIF({Completion Date}, >DATE(2020, 12, 31), {Days Total})
cheers,
Kelly
-
You are missing the Completion Date range and your syntax is not correct. Should be,
= AVG(COLLECT([Days Total]:[Days Total], [Completion Date]:[Completion Date], DATE(2020,12,31)))
If your data is in a different sheet then,
= AVG(COLLECT({Days Total Range}, {Completion Date Range}, DATE(2020,12,31)))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 62 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!