Finding median using array formula

Topic

New Reply

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 (Age0)?

Thanks in advance,
Erik

Reply | Quote

Replies

For Age 0 and Category = “A”:

=MEDIAN(IF(($A$2:$A$140)*($B$2:$B$14=”A”),$A$2:$A$14))

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

Reply | Quote

For Age 0 and Category = “A”:

=MEDIAN(IF(($A$2:$A$140)*($B$2:$B$14=”A”),$A$2:$A$14))

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

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

Reply | Quote