Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Seattle, Washington, USA
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts
    In my incorrigible formula-ignorance I think "finding median using array formula" is what I need. Here's what I'd like to do:

    In some simple patient data, Column A contains ages (with 0 entered where age is unavailable) and Column B contains categories (A-D, with 0 entered where category is unavailable). No sorting allowed.

    Example:

    Age|Category
    33|A
    22|A
    0|A
    56|B
    65|B
    66|B
    77|B
    88|B
    33|B
    0|B
    36|B
    33|C
    0|B

    What formula can I use to find the median age where for example (Category=A) AND (Age<>0)?

    Thanks in advance,
    Erik

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    For Age <> 0 and Category = "A":

    =MEDIAN(IF(($A$2:$A$14<>0)*($B$2:$B$14="A"),$A$2:$ A$14))

    as an array formula (confirm with Ctrl+Shift+Enter). Adjust the ranges as needed.

  3. #3
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Seattle, Washington, USA
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='779249' date='11-Jun-2009 00:53']For Age <> 0 and Category = "A":

    =MEDIAN(IF(($A$2:$A$14<>0)*($B$2:$B$14="A"),$A$2:$ A$14))

    as an array formula (confirm with Ctrl+Shift+Enter). Adjust the ranges as needed.[/quote]

    Thank you! I was missing the asterisk * and Excel didn't like that.

Posting Permissions

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