Median If?

[John McGraw]

Is there any trick I can pull to get the
median for a series of data among only the data chosen by a condition?

Lets say column A has lables indicating the age of people, and column B has their height. Can I somehow write a formula to get the median height for people who's age is 20 for example?

I figured a way to do this for the average. (SUMIF(A:A,"=20",B:B)/COUNTIF(A:A,"=20))

But the other functions like median, min, max, etc have me stumped.

Thanks for any help.

 

[Aladin Akyurek]

On 2002-02-26 23:58, John McGraw wrote:
Is there any trick I can pull to get the
median for a series of data among only the data chosen by a condition?

Lets say column A has lables indicating the age of people, and column B has their height. Can I somehow write a formula to get the median height for people who's age is 20 for example?

I figured a way to do this for the average. (SUMIF(A:A,"=20",B:B)/COUNTIF(A:A,"=20))

But the other functions like median, min, max, etc have me stumped.

Thanks for any help.

Hi John,

Array-enter

=MEDIAN(IF(A2:A10=20,B2:B10))

where A2:A10 houses the ages and B2:B10 corresponding heights.

You need to hit control+shift+enter at the same time, not just enter, to array-enter a formula.

Conditional MIN and conditional MAX follow the same logic.

By the way, when you use the equality test, your conditional average can be simplified a bit:

=SUMIF(A:A,20,B:B)/MAX(1,COUNTIF(A:A,20))

I added the MAX bit to prevent #DIV/0! in case the condition ("=20") does not hold.

Aladin

Addendum: John, you can also put a condition in a cell of its own and use that cell in the formulas like in

=SUMIF(A:A,E1,B:B)/MAX(1,COUNTIF(A:A,E1))

=SUMIF(A:A,"<"&E1,B:B)/MAX(1,COUNTIF(A:A,"<"&E1))

etc.
This message was edited by Aladin Akyurek on 2002-02-27 00:31
This message was edited by Aladin Akyurek on 2002-02-27 03:05

 

[John McGraw]

Aladin,

You are amazing! I dont know how you are always able to answer so many questions so precisely!

Thanks,

John