Reference another sheet in IF/MEDIAN function

Brian Strock

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!

Mark Cronk

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

John Pudar

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

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

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

Brian Strock

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

Mark Cronk

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

Mark