Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return the row number of Median value of a column | Excel Worksheet Functions | |||
Lookup to return count, median, lower quartile, upper quartile&ave | Excel Discussion (Misc queries) | |||
Summing numbers in a list when they meet a critieria in another co | Excel Discussion (Misc queries) | |||
Retrieve and group row data by multiple critieria | Excel Worksheet Functions | |||
calculate a MEDIAN using multiple criteria? | Excel Worksheet Functions |