Median If?
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Median If?

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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.

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,871
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

    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 ]

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Aladin,

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

    Thanks,

    John

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com