# Thread: Finding median using array formula

1. 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)?

Erik

2. 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. [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
•