Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Median with Criteria
Hello,
I want to write a formula that calculates the median of a set of values specified by some criteria. For example, if you have the following in A and B: 1 1 1 2 2 3 1 7 2 6 1 8 1 9 2 4 2 3 2 4 1 5 1 6 2 3 I want to calculate the median of the values in column B that have a 1 in column A (in this case, median = 6). Does anyone have a good idea for how to do that? Thanks, |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Median with Criteria
Hi,
Try this ARRAY formula =MEDIAN(IF(A1:A13=1,B1:B13)) This is an array formula which must be entered by pressing CTRL+Shift+Enter and not just Enter. If you do it correctly then Excel will put curly brackets around the formula {}. You can't type these yourself. If you edit the formula you must enter it again with CTRL+Shift+Enter. -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Demosthenes" wrote: Hello, I want to write a formula that calculates the median of a set of values specified by some criteria. For example, if you have the following in A and B: 1 1 1 2 2 3 1 7 2 6 1 8 1 9 2 4 2 3 2 4 1 5 1 6 2 3 I want to calculate the median of the values in column B that have a 1 in column A (in this case, median = 6). Does anyone have a good idea for how to do that? Thanks, |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Median with Criteria
Thanks Mike! Hit the nail on the head. I also found another very helpful
discussion on the same topic shortly after I posted. You can find it he http://www.microsoft.com/office/comm...=en-us&m=1&p=1 "Mike H" wrote: Hi, Try this ARRAY formula =MEDIAN(IF(A1:A13=1,B1:B13)) This is an array formula which must be entered by pressing CTRL+Shift+Enter and not just Enter. If you do it correctly then Excel will put curly brackets around the formula {}. You can't type these yourself. If you edit the formula you must enter it again with CTRL+Shift+Enter. -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Demosthenes" wrote: Hello, I want to write a formula that calculates the median of a set of values specified by some criteria. For example, if you have the following in A and B: 1 1 1 2 2 3 1 7 2 6 1 8 1 9 2 4 2 3 2 4 1 5 1 6 2 3 I want to calculate the median of the values in column B that have a 1 in column A (in this case, median = 6). Does anyone have a good idea for how to do that? Thanks, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to get the median of cells that meet a given criteria in excel | Excel Worksheet Functions | |||
Median | Excel Discussion (Misc queries) | |||
Max/Min/Median | Excel Worksheet Functions | |||
Finding the median of numbers meeting criteria | Excel Discussion (Misc queries) | |||
calculate a MEDIAN using multiple criteria? | Excel Worksheet Functions |