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
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!