#incorrect argument set error when COLLECT criteria not met

nmhammon

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.

jmyzk_cloudsmart_jp

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.

https://app.smartsheet.com/b/publish?EQBCT=27b44fb326dd4313bf516e92e0fc61c9

jmyzk_cloudsmart_jp

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.

https://app.smartsheet.com/b/publish?EQBCT=27b44fb326dd4313bf516e92e0fc61c9

nmhammon

Fantastic! This worked perfectly, thank you.