# In Excel, getting the MEDIAN of a set based on a text pattern match

October 26, 2012 10:19 PM Subscribe

In Excel, I have two columns. Column A has many rows of names (e.g., Mary, Maryjane, Maryanne, Anne, Jane) and in Column B I have their ages (e.g., 4, 20, 40, 10). I would like to find the MEDIAN age of all the "mary's"; i.e., both "mary" and "maryanne", etc.
Is this possible? I have been getting errors on variations of {=MEDIAN(IF(A1:A4="*mary*",B1:B4))} constructions...
I do not want to use VBA.
thank you!

Just use a filter for any cell that contains the word Mary, and then do the calculations on that subset.

posted by Alice Russel-Wallace at 11:01 PM on October 26, 2012

posted by Alice Russel-Wallace at 11:01 PM on October 26, 2012

I apologize in advance - i'm on my phone and this may not work. You should be able to do this with an array formula (Ctrl Shift Enter)

assuming you're using columns A and B and have 5 values, try this but press Ctrl Shift Enter when entering in the formula (brackets should appear around the formula)

= MEDIAN(IF(ISNUMBER(SEARCH("mary",A1:A5)),B1:B5))

posted by hot soup at 11:22 PM on October 26, 2012 [3 favorites]

assuming you're using columns A and B and have 5 values, try this but press Ctrl Shift Enter when entering in the formula (brackets should appear around the formula)

= MEDIAN(IF(ISNUMBER(SEARCH("mary",A1:A5)),B1:B5))

posted by hot soup at 11:22 PM on October 26, 2012 [3 favorites]

hi calgirl, i think i've seen that page as well. google searches for these and related terms are pretty slim. alice, that's definitely what i want to do but i'm not sure how i would do that filter – am not an Excel user – and things like FIND, MATCH, ISERROR, etc. don't seem to be right. thanks!

posted by noway at 11:24 PM on October 26, 2012

posted by noway at 11:24 PM on October 26, 2012

hot soup! you have taught me a new way to fish. thank you!

posted by noway at 11:27 PM on October 26, 2012

posted by noway at 11:27 PM on October 26, 2012

This thread is closed to new comments.

=MEDIAN(IF(A:A = "x",IF(B:B<>"",B:B, ""),"")

I'm not sure I understand the details...can you make use of this?

posted by calgirl at 10:33 PM on October 26, 2012