AVG COLLECT formula

System

This discussion was created from comments split from: AverageIf with multiple criterion.

Samantha Ludwig

Hi @Paul Newcome,

I'm attempting a similar formula. I want to average days to complete if setup status is not "already exists" or "cancelled" but the formula seems to stop at the "AND" function:

=AVG(COLLECT([# Total Days to Setup Complete]:[# Total Days to Setup Complete], [Setup Status]:[Setup Status], AND(@cell <> "Already Exists", @cell <> "Cancelled")))

The formula is just returning the average days to setup complete without the exclusions. The @cell references are not highlighting like I would expect.

I also tried:

=AVG(COLLECT([# Total Days to Setup Complete]:[# Total Days to Setup Complete], [Setup Status]:[Setup Status], AND([Setup Status]1 <> "Already Exists", [Setup Status]1 <> "Cancelled")))

And received a "#divide by zero" error.

**Nevermind, solved it myself. Changed AND to OR, it worked fine.

Thank you,

Samantha Ludwig

Michelle Choate 2

Hi @Samantha Ludwig !

I will answer it down here so you can mark it completed and it will stop showing on the Unanswered Questions page :)

Switch the AND formula for an OR formula.