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
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.4K Get Help
 325 Global Discussions
 197 Industry Talk
 418 Announcements
 4.2K Ideas & Feature Requests
 127 Brandfolder
 154 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 278 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!