Collecting valued with IF Dependencies

eLIZo
eLIZo ✭✭✭✭
edited 11/17/22 in Formulas and Functions

I'm stumped in trying to average the values in a column ( CMO Dispo) that meets the criteria of

  • CMO column - "Agilent"
  • DOM column - 2022 year

Current non-working formula: =AVG(COLLECT([CMO Dispo (Business Days)]:[CMO Dispo (Business Days)], MFG:MFG, ="Agilent", DOM:DOM, =YEAR(2022, 1, 1)))

I'm pretty sure its not working because there are some error cells in my CMO Dispo column, but I don't know how to get it to skip cells with a error code.

Tags:

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Try this:

    =AVG(COLLECT([CMO Dispo (Business Days)]:[CMO Dispo (Business Days)], MFG:MFG, @cell="Agilent", DOM:DOM, IFERROR(YEAR(@cell), 0) = 2022)

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • eLIZo
    eLIZo ✭✭✭✭
    edited 11/18/22

    It's still not working. The error cells are in the CMO Dispo (Business Days) column, not the year column.

    I tried to use your tip in a different place in the formula, but its still not working.

    =AVG(COLLECT(IFERROR([CMO Dispo (Business Days)]:[CMO Dispo (Business Days)], ""), MFG:MFG, @cell = "Agilent", DOM:DOM, IFERROR(YEAR(@cell), 0) = 2022))

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Ah. You will need to get rid of that error on the source sheet then. You can wrap whatever formula you are using in that column in an IFERROR to output a blank like so:

    =IFERROR(original_formula, "")

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!