Determining median values with nested IF and AND array formulas

3
votes

I'm trying to create two similar array formulas:

One is to determine the median value of the cells in a column (B), but only if there are both corresponding blank values in column J, and there are numeric values in column D. (D and J are in separate sheets.)

The other formula is the do the same thing, except it will only determine the median for the cells in B for which the corresponding cells in column J are not blank.

I should note I am making sure to enter them in with command-shift-enter, since they are array formulas. However, it's coming out to zero for both of them. I've tried for a long time and can't figure out why. I'm new to Excel formulas, so please let me know if I should clarify my question.

First formula:

=MEDIAN(IF(

 AND('Raw Data'!$J$3:'Raw Data'!$J$999="", ISNUMBER('Raw Data'!$D$3:'Raw Data'!$D$999)),

 B$6:B$1002))

Second formula:

=MEDIAN(IF(

 AND(NOT(ISBLANK('Raw Data'!$J$3:'Raw Data'!$J$999)), ISNUMBER('Raw Data'!$D$3:'Raw Data'!$D$999)),

 B$6:B$1002))
1
excelexcel-formula

1 Answers

5
votes

Array equivalent of AND is multiplication *:

=MEDIAN(IF(('Raw Data'!$J$3:$J$999="")*
            ISNUMBER('Raw Data'!$D$3:$D$999),
            B$6:B$1002)
       )

with array entry (CTRL+SHIFT+ENTER).

For second formula:

=MEDIAN(IF(NOT(ISBLANK('Raw Data'!$J$3:$J$999))*
           ISNUMBER('Raw Data'!$D$3:$D$999),
           B$6:B$1002)
       )

also with array entry.

Explanation why AND not working.

What you expect is that AND({TRUE,FALSE,FALSE},{TRUE,TRUE,FALSE}) returns {TRUE,FALSE,FALSE}.

However, AND takes array of boolean values and returns single boolean value - which indicates whether all values are TRUE or FALSE.

So, AND({TRUE,FALSE,FALSE},{TRUE,TRUE,FALSE}) returns FALSE because not all values are TRUE.

But, multiplication {TRUE,FALSE,FALSE}*{TRUE,TRUE,FALSE} works exactly as you need it - it returns {TRUE*TRUE,FALSE*TRUE,FALSE*FALSE} = {TRUE, FALSE, FALSE}