MEDIAN on another sheet, sometimes throwing #INCORRECT ARGUMENT SET
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 L. Reedy, MS Software Engineering
Coordinator, HR Project MWF
myLearning LMS Administrator
Learning Technology
Ascension  Enterprise Projects
Best Answer

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.
Stephanie L. Reedy, MS Software Engineering
Coordinator, HR Project MWF
myLearning LMS Administrator
Learning Technology
Ascension  Enterprise Projects
Answers

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.

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.
Stephanie L. Reedy, MS Software Engineering
Coordinator, HR Project MWF
myLearning LMS Administrator
Learning Technology
Ascension  Enterprise Projects
Help Article Resources
Categories
Check out the Formula Handbook template!