Median return for multiple critieria
 

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

Median return for multiple critieria
 

Angela

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

Array entered with Ctrl+Shift+Enter

Mike

"Angela" wrote:

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

Median return for multiple critieria
 

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" wrote:

Angela

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

Array entered with Ctrl+Shift+Enter

Mike

"Angela" wrote:

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

Median return for multiple critieria
 

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


"Angela" wrote:

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" wrote:

Angela

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

Array entered with Ctrl+Shift+Enter

Mike

"Angela" wrote:

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

Median return for multiple critieria
 

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.

--
__________________________________
HTH

Bob

"Angela" wrote in message
...
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" wrote:

Angela

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

Array entered with Ctrl+Shift+Enter

Mike

"Angela" wrote:

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

Median return for multiple critieria
 

Mike/Bob

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

Regards
Angela


"Bob Phillips" wrote:

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.

--
__________________________________
HTH

Bob

"Angela" wrote in message
...
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" wrote:

Angela

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

Array entered with Ctrl+Shift+Enter

Mike

"Angela" wrote:

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