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

[email protected]

Tags:

Best Answer

  • Stephanie Reedy
    Stephanie Reedy ✭✭✭
    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

    [email protected]

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.

  • Stephanie Reedy
    Stephanie Reedy ✭✭✭
    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

    [email protected]

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!