conditional median in excel worksheet

Question

I want to calculate a conditional median.

The formula =MEDIAN(K3851:K4792;$O3851:$O4792) works. The step I cannot make is the one where the calculation is restricted to when K<1. It seems easy enough, but I get the error code "#NAME?".

I would greatly appreciate your help.

Answer 1

You can use an array formula¹ to set up the condition. The array formula will pick up blank cells so you will have to add a condition to discard them as well.

=MEDIAN(IF(K3851:K4792<1; IF(K3851:K4792<>""; $O3851:$O4792)))

PROOF

        

¹ _{Array formulas need to be finalized with Ctrl+Shift+Enter↵. Once entered into the first cell correctly, they can be filled or copied down or right just like any other formula. See Guidelines and examples of array formulas for more information.}