#incorrect argument set error when COLLECT criteria not met
The following formula works perfectly when all listed criteria are met. However, I get an "incorrect argument set" error if any of the criteria is unmet. I added the iferror hoping this would solve the problem, but it did not.
=IFERROR(MEDIAN(COLLECT({Complete FY24 list}, {Complete FY24 list Reporting Bin}, [Product Reporting Group]@row, {Complete FY24 list quarter}, "1")), "0")
It is expected that some criteria will not be met, and I want those cells to either be blank, return a "n/a," or zero.
I also want to build out my sheet with the formulas for future quarters, which all currently display the #incorrect argument set error.
Best Answer
-
Hi @nmhammon
This is an intriguing case!
The MEDIAN function expects more than one value. So, if the COLLECT function returns an empty range, it will cause an error. (Divide by zero)
So, if you change the MEDIAN to SUM, you will not get an error. (Please check the Sheet Summary of the 2nd and 3rd images below, [Quoter Sum w/o Count Check] field.)
To make sure you don't get an error, you need to make sure COUNT(COLLECT( ))>0.
The demo below uses Sheet Summary, but the function structures are identical.
IF(COUNT(COLLECT([Complete FY24 list]:[Complete FY24 list], [Complete FY24 list Reporting Bin]:[Complete FY24 list Reporting Bin], [Product Reporting Group]#, [Complete FY24 list quarter]:[Complete FY24 list quarter], [Complete FY24 list quarter]#)) > 0, MEDIAN(COLLECT([Complete FY24 list]:[Complete FY24 list], [Complete FY24 list Reporting Bin]:[Complete FY24 list Reporting Bin], [Product Reporting Group]#, [Complete FY24 list quarter]:[Complete FY24 list quarter], [Complete FY24 list quarter]#)), "n/a")
or
IF(COUNT(COLLECT( ... ) > 0, MEDIAN(COLLECT( ... )), "n/a")
You can check how the formula works in the published demo sheet by changing the 1st and 2nd fields, quoter, and group.
Answers
-
Hi @nmhammon
This is an intriguing case!
The MEDIAN function expects more than one value. So, if the COLLECT function returns an empty range, it will cause an error. (Divide by zero)
So, if you change the MEDIAN to SUM, you will not get an error. (Please check the Sheet Summary of the 2nd and 3rd images below, [Quoter Sum w/o Count Check] field.)
To make sure you don't get an error, you need to make sure COUNT(COLLECT( ))>0.
The demo below uses Sheet Summary, but the function structures are identical.
IF(COUNT(COLLECT([Complete FY24 list]:[Complete FY24 list], [Complete FY24 list Reporting Bin]:[Complete FY24 list Reporting Bin], [Product Reporting Group]#, [Complete FY24 list quarter]:[Complete FY24 list quarter], [Complete FY24 list quarter]#)) > 0, MEDIAN(COLLECT([Complete FY24 list]:[Complete FY24 list], [Complete FY24 list Reporting Bin]:[Complete FY24 list Reporting Bin], [Product Reporting Group]#, [Complete FY24 list quarter]:[Complete FY24 list quarter], [Complete FY24 list quarter]#)), "n/a")
or
IF(COUNT(COLLECT( ... ) > 0, MEDIAN(COLLECT( ... )), "n/a")
You can check how the formula works in the published demo sheet by changing the 1st and 2nd fields, quoter, and group.
-
Fantastic! This worked perfectly, thank you.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!