MEDIAN on another sheet, sometimes throwing #INCORRECT ARGUMENT SET

Stephanie Reedy

I have a summary sheet, pointing to another sheet where I'm gather median hours based on a specific criteria in a column formula. I have a Coordinator Column as the reference point. The other sheet should use the Coordinator and get the median of my Days column based on other criteria.

=IFERROR(IF(OR([Total NEW Tasks]@row = 0, [Total Requests]@row = 0), "", MEDIAN(COLLECT({FY23Processing_Build}, {FY23Processing_Coordinator}, Coordinator@row, {FY23Processing_Metrics}, 1))), "")

• {FY23Processing_Build} is the Days from the other sheet that is just part of their task
• {FY23Processing_Coordinator} is the matching Coordinator
• {FY23Processing_Metrics} is the flag to use this particular metric

Problem is I have 1 person that has all of their NEW Tasks with blank Processing_Build days (rest of my Median tracking columns have data so I can't completely change their Metrics flag to 0). I can't seem to be able to figure out how to get that to result in a "".

Thoughts?

Stephanie Reedy

Thank you! I woke with a thought very similar to what you wrote. ;) Guess I just needed sleep.

IFERROR(IF(OR([Total NEW Tasks]@row = 0, [Total Requests]@row = 0, SUMIF({FY23Processing_Coordinator}, Coordinator@row, {FY23Processing_Build}) = 0), "", MEDIAN(COLLECT({FY23Processing_Build}, {FY23Processing_Coordinator}, Coordinator@row, {FY23Processing_Metrics}, 1))), "")

I couldn't do just SUM({FY23Processing_Build}) = 0 as it would never happen since there are other Coordinators.

Chris Shifflett

To make the formula return "" for the case where the [Total NEW Tasks] is equal to 0 or [Total Requests] is equal to 0, you can add the "OR" function inside the "IF" function like this:

=IFERROR(IF(OR([Total NEW Tasks]@row = 0, [Total Requests]@row = 0), "", MEDIAN(COLLECT({FY23Processing_Build}, {FY23Processing_Coordinator}, Coordinator@row, {FY23Processing_Metrics}, 1))), "")

In this formula, the "OR" function checks if either of the two conditions ([Total NEW Tasks] = 0 or [Total Requests] = 0) is true, and if so, the formula returns "". Otherwise, it returns the median calculation using the COLLECT function.

For the case where all NEW Tasks have blank Processing_Build days, you can add another condition to the "IF" function to check if the SUM of the {FY23Processing_Build} column is equal to 0, like this:

=IFERROR(IF(OR([Total NEW Tasks]@row = 0, [Total Requests]@row = 0, SUM({FY23Processing_Build}) = 0), "", MEDIAN(COLLECT({FY23Processing_Build}, {FY23Processing_Coordinator}, Coordinator@row, {FY23Processing_Metrics}, 1))), "")

This way, if the SUM of {FY23Processing_Build} is equal to 0, the formula will return "", otherwise it will return the median calculation.

Stephanie Reedy

Thank you! I woke with a thought very similar to what you wrote. ;) Guess I just needed sleep.

IFERROR(IF(OR([Total NEW Tasks]@row = 0, [Total Requests]@row = 0, SUMIF({FY23Processing_Coordinator}, Coordinator@row, {FY23Processing_Build}) = 0), "", MEDIAN(COLLECT({FY23Processing_Build}, {FY23Processing_Coordinator}, Coordinator@row, {FY23Processing_Metrics}, 1))), "")

I couldn't do just SUM({FY23Processing_Build}) = 0 as it would never happen since there are other Coordinators.