I am trying to modify an existing formula using (AVG(COLLECT and want to add a new value

Scott Shutter

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

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