In Excel, getting the MEDIAN of a set based on a text pattern match October 26, 2012 10:19 PMSubscribe

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) 5 users marked this as a favorite

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

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]

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

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

« Older I'm moving into a new place! ... | I'm giving someone a ride to S... Newer »

=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