Median return for multiple critieria

A

Angela

I would like to calculate the median in column P but only where in column Q
there is a 'Yes 'entry.
 
M

Mike H

Angela

=MEDIAN(IF(Q1:Q20="Yes",P1:p20,FALSE))

Array entered with Ctrl+Shift+Enter

Mike
 
A

Angela

Hi Mike

Unfortunately this didn't work it returned #value!. Maybe I haven't given
enough information so I will try and elaborate a bit more. Where in column Q
the is entry is 'Yes' I need the median of the values in column P that only
relate to these 'Yes' entries. Hopefully once I have this formula I will be
able to change it so where in column Q the cells are blank I can caluclate
the median of the corresponding values in column P.

Thanks
Angela
 
M

Mike H

Angela,

That's exactly what the formula I gave you does. take this shortened set of
data data
Col P Col Q
1 Yes
99 Yes
99999
4 Yes

The formula
=MEDIAN(IF(Q1:Q4="Yes",P1:p4,FALSE))
Returns 4 which is the median of 1, 99 & 4
but it only does so if the formula is array entered with Ctrl+Shift+ Enter
If you've entered the formula correctly then I suggest you check your data
are what you think they are.


Mike
 
B

Bob Phillips

Did you array enter it, Ctrl-Shift-Enter, not just Enter?

If you do it correctly, it will be enclosed in braces {...} in the formula
bar.
 
A

Angela

Mike/Bob

I finally got there, thank you very much for your assistance.

Regards
Angela
 
Top