Collecting valued with IF Dependencies
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.
Answers
-
Try this:
=AVG(COLLECT([CMO Dispo (Business Days)]:[CMO Dispo (Business Days)], MFG:MFG, @cell="Agilent", DOM:DOM, IFERROR(YEAR(@cell), 0) = 2022)
-
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))
-
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, "")
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!