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

October 26, 2012 10:19 PM

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!
posted by noway to Computers & Internet (5 answers total)

here's a sample that I found online at stackoverflow.com

=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

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

Best answer: 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]

Response by poster: 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

Response by poster: hot soup! you have taught me a new way to fish. thank you!
posted by noway at 11:27 PM on October 26, 2012