IFERROR Function Struggles
I have a working formula that I need to add an IFERROR function to so that the cell is blank when there are no values to satisfy the formula instead of receiving an #INCORRECT ARGUEMENT SET response.
Here is the working formula without the IFERROR:
=MEDIAN(COLLECT({Resi - PC2 Testing Duration}, {Resi - Enter Stage 7 Date}, AND(@cell >= $[Start Date]$1, @cell <= $[End Date]$1), {Resi - Onboarding Specialist}, @cell = "Emerald Bluiett"))
and here it is with the IFERROR added that isn't working:
=IFERROR(MEDIAN(COLLECT({Resi - PC2 Testing Duration}, {Resi - Enter Stage 7 Date}, AND(@cell >= $[Start Date]$1, @cell <= $[End Date]$1), {Resi - Onboarding Specialist}, @cell = "Emerald Bluiett")), "")
I slept on this problem since yesterday and the "light" has yet to turn on as to what my mistake is…
Best Answer
-
I have run into this before, and it is always frustrating. Try an IF/ISERROR combo.
=IF(ISERROR(original_formula), "", original_formula)
Answers
-
There is nothing obviously wrong with the formulas you have in your post. Are you able to provide a screenshot of the formula causing the error open in the sheet as if you are about to edit it?
-
in the COLLECT criteria, try adding a condition to not calculate for blank cells or cells you don't want to include in the MEDIAN function.
Sincerely,
Jacob Stey
-
I assume this screenshot is what you meant for me to share:
-
What do you get if you use a COUNTIFS (temporarily) along the lines of:
=COUNTIFS({Duration Range}, ISNUMBER(@cell), {Date range}, AND(date_criteria), {Specialist Range}, specialist_criteria)
-
With more testing, it does appear as though adding the IFERROR to my working formula 1) still allows the Median and Collect functions to work but 2) it does not prevent displaying an #INCORRECT ARGUEMENT SET error message if there is no data available that matches the criteria in the formula. So, the original formula works as expected:
=MEDIAN(COLLECT({Resi - PC2 Testing Duration}, {Resi - Enter Stage 7 Date}, AND(@cell >= $[Start Date]$1, @cell <= $[End Date]$1), {Resi - Onboarding Specialist}, @cell = "Emerald Bluiett"))
and the formula, when modified with IFERROR, still does the calculations but does not prohibit the error from being displayed:
=IFERROR(MEDIAN(COLLECT({Resi - PC2 Testing Duration}, {Resi - Enter Stage 7 Date}, AND(@cell >= $[Start Date]$1, @cell <= $[End Date]$1), {Resi - Onboarding Specialist}, @cell = "Emerald Bluiett")), "")
Does IFERROR need to be placed somewhere else in the formula for it to prohibit displaying error codes?
-
I have run into this before, and it is always frustrating. Try an IF/ISERROR combo.
=IF(ISERROR(original_formula), "", original_formula)
-
That was it, @Paul Newcome. It was a little challenge to get the parenthesis correct but eventually got there. Now to do the same thing to other formulae in my metric sheet so my dashboard shows a blank instead of an error message when there is zero data meeting the criteria. Thank you.
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.1K Get Help
- 444 Global Discussions
- 142 Industry Talk
- 472 Announcements
- 5K Ideas & Feature Requests
- 83 Brandfolder
- 150 Just for fun
- 71 Community Job Board
- 489 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 301 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!