I am trying to modify an existing formula using (AVG(COLLECT and want to add a new value
Current formula is as follows: =IFERROR(AVG(COLLECT({BR - Campaign Roadmap Update Ref Duration}, {BR - Campaign Roadmap Update Campaign Status}, "In Development", {BR - Campaign Roadmap Update Vehicle}, [Vehicle Type]@row)), "0")
Now I would like to add an additional value to the AVG COLLECT function. In addition to "In Development" - a campaign status value, I would also like to use the AVG COLLECT function on campaigns that are "Complete" as well. I have tried using AND and OR, but no luck.
Any suggestions would be greatly appreciated.
Best Regards,
Scott
Answers
-
I had just solved for a similar formula of my own last week. Try this, repeating the "COLLECT" statement with the additional campaign status within the AVG ( ) section of the formula:
=IFERROR(AVG((COLLECT({BR - Campaign Roadmap Update Ref Duration}, {BR - Campaign Roadmap Update Campaign Status}, "In Development", {BR - Campaign Roadmap Update Vehicle}, [Vehicle Type]@row)), (COLLECT({BR - Campaign Roadmap Update Ref Duration}, {BR - Campaign Roadmap Update Campaign Status}, "Complete", {BR - Campaign Roadmap Update Vehicle}, [Vehicle Type]@row))), "0")
-
Hi Laura and thanks for the help. I get "UNPARSEABLE" with that. Is the last part of the phrase, the @row statement, need to be repeated? I tried removing it and no good. Am I missing something there?
=IFERROR(AVG((COLLECT({ANZ - Campaign Roadmap Update Ref Duration}, {ANZ - Campaign Roadmap Update Range 1}, "In Development", COLLECT({BR - Campaign Roadmap Update Ref Duration}, {BR - Campaign Roadmap Update Campaign Status}, "Complete", {BR - Campaign Roadmap Update Range 1}, [Vehicle Type]@row))), "0")) -- #INCORRECT ARGUMENT
I simply am adding the additional status to be picked up and averaged into the total development time. Thoughts?
Cheers - Scott
-
Hi Scott,
I see missing parentheses around the COLLECT statements. Did you try a copy/paste of the formula I provided? It is working for me. The @row statement is repeated in each collect statement because it's part of the criteria for each selection.
Laura
-
I tried that but it doesn't then average the values for both status criteria, "In Development" and "Complete", as I mentioned it shows a "0" value which can't be right. How are you testing this? I just tried again with diffrent campaign types, using @row, and all the existing AVG values go to "0". Thinking something is off. Hmmmm. Thoughts?
With great appreciation,
Scott
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!