Collecting valued with IF Dependencies

eLIZo

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.

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)

eLIZo

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

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