Formula Error
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)
Best Answer
-
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))
Answers
-
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.
-
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.
-
Ah. That information helps.
And you still get the error even with the IFERROR statement?
-
Hi Paul,
Yes. Any other ideas on how to fix this error?
-
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))
-
Hi Paul,
It worked! Thank you so much!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 497 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!