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, |
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, |
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, |
All times are GMT +1. The time now is 12:06 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com