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

Scott Shutter
Scott Shutter ✭✭✭
edited 07/27/21 in Formulas and Functions

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

  • Laura B.
    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")

  • 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.
    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

  • 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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!