Average Collect Issues
Hello! I'm trying to write a formula that will average the amount of time a type of request takes to complete by month. I'm referencing another sheet in this formula and just can't get it to work. Here's what I have so far:
=AVG(COLLECT{Hours to Complete}, ISNUMBER(@cell), {Request Type}, "Quote Request", {Submitted Date}, YEAR(@cell) = 2023, {Submitted Date}, MONTH(@cell) = 1)
I'm not sure if I need the "IS NUMBER" or not, but I don't want it to count blank cells.
Anyone have ideas? Thanks in advance!
Best Answer
-
The ISNUMBER isn't needed because the AVG function omits blanks.
=IFERROR(AVG(COLLECT([Hours to Complete]:[Hours to Complete], [Request Type]:[Request Type], "Quote Request", [Submitted Date]:[Submitted Date], AND(VALUE(YEAR(@cell)) = 2023, VALUE(MONTH(@cell)) = 3))), "")
=IFERROR(AVG(COLLECT({Hours to Complete}, {Request Type}, "Quote Request", {Submitted Date}, AND(VALUE(YEAR(@cell)) = 2023, VALUE(MONTH(@cell)) = 3))), "")
If you have any missing Submitted Dates there might be an error so make sure they are all filled in.
Answers
-
The ISNUMBER isn't needed because the AVG function omits blanks.
=IFERROR(AVG(COLLECT([Hours to Complete]:[Hours to Complete], [Request Type]:[Request Type], "Quote Request", [Submitted Date]:[Submitted Date], AND(VALUE(YEAR(@cell)) = 2023, VALUE(MONTH(@cell)) = 3))), "")
=IFERROR(AVG(COLLECT({Hours to Complete}, {Request Type}, "Quote Request", {Submitted Date}, AND(VALUE(YEAR(@cell)) = 2023, VALUE(MONTH(@cell)) = 3))), "")
If you have any missing Submitted Dates there might be an error so make sure they are all filled in.
-
This worked! Thank you :)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 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!