Reference another sheet in IF/MEDIAN function

Options

Hello-

I'm trying to calculate the median of a column given certain conditions are met (before a certain date based on one column and certain values are blank in two other columns). I've gotten it to work if I use this formula on the same sheet:

=IF(AND([5 - Production]@row < DATE(2021, 7, 1), ISBLANK([Channel Partner]@row), ISBLANK([PMO Removed]@row)), MEDIAN([Total PMO Active days]:[Total PMO Active days], ""))

What I'd ultimately like to do is have this calculation done on a separate sheet with references to the original, but I can't seem to get it to work properly. My very novice stab at it was to replace anything in [ ] with sheet references in { }.

=IF(AND({2021 Active Translations Range 1}@row < DATE(2021, 7, 1), ISBLANK({2021 Active Translations Range 2}@row), ISBLANK({2021 Active Translations Range 7}@row)), MEDIAN({2021 Active Translations Range 4}:{2021 Active Translations Range 4}, ""))

The range in each of the references is a selection of the entire column.

Any thoughts on how to accomplish this? Or is there a better way altogether?

Thanks!

Best Answer

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓
    Options

    Try:

    =MEDIAN(Collect({2021 Active Translations Range 4}, {2021 Active Translations Range 1}, @cell < DATE(2021, 7, 1), {2021 Active Translations Range 2}, isblank(@cell), {2021 Active Translations Range 7}, isblank(@cell))

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Answers

  • John Pudar
    John Pudar ✭✭✭✭
    Options

    Hi @Brian Strock,

    A reference can point at an entire column, so there's no need to use the Column:Column range feature in this case. You should be able to just use the reference alone:

    =IF(AND({2021 Active Translations Range 1}@row < DATE(2021, 7, 1), ISBLANK({2021 Active Translations Range 2}@row), ISBLANK({2021 Active Translations Range 7}@row)), MEDIAN({2021 Active Translations Range 4}, ""))

  • Brian Strock
    Options

    Thanks, @John Pudar. I've removed the column:column range and it appears the formula now matches yours, but it still gives me an "UNPARSEABLE" error. Any thoughts what else I may be missing?


    =IF(AND({2021 Active Translations Range 1}@row < DATE(2021, 7, 1), ISBLANK({2021 Active Translations Range 2}@row), ISBLANK({2021 Active Translations Range 7}@row)), MEDIAN({2021 Active Translations Range 4}, ""))

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓
    Options

    Try:

    =MEDIAN(Collect({2021 Active Translations Range 4}, {2021 Active Translations Range 1}, @cell < DATE(2021, 7, 1), {2021 Active Translations Range 2}, isblank(@cell), {2021 Active Translations Range 7}, isblank(@cell))

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Brian Strock
    Options

    Thank you, @Mark. That worked. Collect was what I needed. Much appreciated!

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Options

    Hi Brian, Glad you found a solution. Appreciate the vote up. Thank you for contributing to the community.

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!