Can you average a column per year?
I have set-up a metric for the Average of Actual Days to Completion (=AVG({CActual Days to Completion}) column for the whole tracker. The tracker is composed of requests received from 2022 to 2024.
Now my boss has asked me for the Average of Actual Days to Completion for requests received in 2023, only to see how we're doing. Can you please give me a formula regarding this?
Best Answer
-
You would incorporate the COLLECT function.
=AVG(COLLECT({Range To Average}, {Date Range}, IFERROR(YEAR(@cell), 0) = 2023))
Answers
-
You would incorporate the COLLECT function.
=AVG(COLLECT({Range To Average}, {Date Range}, IFERROR(YEAR(@cell), 0) = 2023))
-
I think I need more guidance on this, so I followed the formula as =AVG(COLLECT({Actual Days to Completion},{Date Received}, IFERROR(@cell),0) = 2023)) but I'm getting #UNPARSEABLE result.
-
@Paul Newcome, Thank you; it worked! for the 1st try I forgot to write the YEAR function. Again Thank you!!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!