Median return for multiple critieria

[Angela]

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

 

[Mike H]

Angela

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

Array entered with Ctrl+Shift+Enter

Mike

 

[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

 

[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",P14,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

 

[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.

 

[Angela]

Mike/Bob

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

Regards
Angela