Formula Error

Asha Krishnan

I want to create a formula that returns the median value when the criteria is met and "0" when the criteria is not met (no data). I used IFERROR for this purpose, however I received a #INCORRECT ARGUMENT SET error. Can someone assist me in fixing this formula? I appreciate your help.

This is the formula for your reference: =IFERROR(MEDIAN(COLLECT({Case Data Range 2}, {Case Data Range 1}, CONTAINS("Closed", @cell), {Case Data Range 7}, <>"True", {Case Data Range 6}, [Primary Column]@row, {Case Data Range 3}, >=DATE(2023, 7, 1), {Case Data Range 3}, <DATE(2024, 7, 1))), 0)

Paul Newcome

I would think the IFERROR would take care of it. Lets try this:

=IF(ISERROR(IFERROR(MEDIAN(COLLECT({Case Data Range 2}, {Case Data Range 1}, CONTAINS("Closed", @cell), {Case Data Range 7}, <>"True", {Case Data Range 6}, [Primary Column]@row, {Case Data Range 3}, >=DATE(2023, 7, 1), {Case Data Range 3}, <DATE(2024, 7, 1))), 0)), 0, IFERROR(MEDIAN(COLLECT({Case Data Range 2}, {Case Data Range 1}, CONTAINS("Closed", @cell), {Case Data Range 7}, <>"True", {Case Data Range 6}, [Primary Column]@row, {Case Data Range 3}, >=DATE(2023, 7, 1), {Case Data Range 3}, <DATE(2024, 7, 1))), 0))

Paul Newcome

Syntax looks fine, and that particular error is typically indicating an issue with your ranges. Double check that they are all referencing single columns.

When setting up a cross sheet reference, always give the sheet time to load completely in the creation window. Once it loads completely, the selection automatically reverts back to the "home cell" which is the cell in the top left corner of the sheet. If you select your range before the sheet loads completely, you may not notice that it has shifted. The best indicator that the sheet has loaded completely in the window is the sheet name will appear in bold just below the cross sheet reference name.

Asha Krishnan

Hi Paul,

Thank you for your response! I tried what you said, but the issue still persists. The error occurs only when the COLLECT function fails to gather any values from the range because no rows meet the specified criteria.

Paul Newcome

Ah. That information helps.

And you still get the error even with the IFERROR statement?

Asha Krishnan

Hi Paul,

Yes. Any other ideas on how to fix this error?

Paul Newcome

I would think the IFERROR would take care of it. Lets try this:

=IF(ISERROR(IFERROR(MEDIAN(COLLECT({Case Data Range 2}, {Case Data Range 1}, CONTAINS("Closed", @cell), {Case Data Range 7}, <>"True", {Case Data Range 6}, [Primary Column]@row, {Case Data Range 3}, >=DATE(2023, 7, 1), {Case Data Range 3}, <DATE(2024, 7, 1))), 0)), 0, IFERROR(MEDIAN(COLLECT({Case Data Range 2}, {Case Data Range 1}, CONTAINS("Closed", @cell), {Case Data Range 7}, <>"True", {Case Data Range 6}, [Primary Column]@row, {Case Data Range 3}, >=DATE(2023, 7, 1), {Case Data Range 3}, <DATE(2024, 7, 1))), 0))

Asha Krishnan

Hi Paul,

It worked! Thank you so much!

Paul Newcome

Happy to help. 👍️