2

I want to determine a MEDIAN of values in one column based on meeting conditions in two other columns. I'm using MS Excel.

I know I have to process the MEDIAN function as an ARRAY (ctrl + shft + enter) using an IF function, and this works fine if I only use 1 condition within my IF statement. However, as soon as I try to incorporate 2 conditions using the AND function, I get a median of 0 (i.e., it doesn't work).

How do I calculate a median of values in one column based on matching conditions of more than one other column?

Example:

Let's say I want to find the median of values in C when A = 1 and B = x:

A   B   C
1   x   10
1   x   20
1   y   30
1   y   40
2   x   10
2   x   20
2   y   30
2   y   40
3   x   10
3   x   20
3   y   30
3   y   40
4   x   10
4   x   20
4   y   30
4   y   40
5   x   10
5   x   20
5   y   30
5   y   40

#Here is my attempt using IF and AND:
{=MEDIAN(IF(AND(A2:A21=1,B2:B21="x"),C2:C21))}    #DEOSN'T WORK: should be 15, but it's 0

#subcomponents of above work fine on their own:
{=MEDIAN(IF(A2:A21=1,C2:C21)) }    #equals 25 as expected
{=MEDIAN(IF(B2:B21="x",C2:C21))}   #equals 15 as expected

How do I make this work and/or is there another approach I should be using??

theforestecologist
  • 586
  • 1
  • 9
  • 26

2 Answers2

2

I don't know why it works, but after messing around I found the following accomplishes my goal:

{=MEDIAN(IF((A2:A21=1)*(B2:B21="x"),C2:C21))}   #gives correct 15

(Again, remembering to process it as an array using ctrl + shft + enter)

My guess is that the AND function is only producing a single TRUE or FALSE by default, and therefore can't work in this context (which requires producing an array of 1s and 0s). However, if I process each column as it's own array, I'm producing a string of 1s and 0s for each subcondition. If I then multiply multiple conditional arrays, the result is itself an array of 1s and 0s in which 1s only exist when the desired condition were true for both respective subconditions.

I'm sure someone can confirm/explain this in a comment.

theforestecologist
  • 586
  • 1
  • 9
  • 26
2

Avoid the AND function, use nested IFs instead.

Specifically:

=MEDIAN(IF(A2:A21=1,IF(B2:B21="x",C2:C21)))

The issue is that AND doesn't work as you intend in an array context -- it doesn't AND each pair of elements and produce an array, it ANDs all the elements of both arrays to give a single scalar result.

Your original formula is evaluating the entire AND call to a single output of "FALSE" (because it's not the case that every element in both arrays satisfies the comparison).

bigjoec
  • 33
  • 6