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…

Find more posts tagged with

Formulas

Accepted answers

Paul Newcome

I have run into this before, and it is always frustrating. Try an IF/ISERROR combo.

=IF(ISERROR(original_formula), "", original_formula)

All comments

Paul Newcome

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?

SteyJ

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.

GMichal

I assume this screenshot is what you meant for me to share:

Paul Newcome

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)

GMichal

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:

Does IFERROR need to be placed somewhere else in the formula for it to prohibit displaying error codes?

Paul Newcome

I have run into this before, and it is always frustrating. Try an IF/ISERROR combo.

=IF(ISERROR(original_formula), "", original_formula)

GMichal

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.

Paul Newcome

Happy to help. 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.