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

Find more posts tagged with

Accepted answers

All comments

Laura B.

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")

Scott Shutter

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

Laura B.

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

Scott Shutter

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.