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.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!