Excel 2007 : Calculating the median using multiple criteria

[scubadiver007]

I have two columns of text and I need to calculate the median for a third column of data for every combination of text from the two first columns. For example I need to work out the median for all values from the following combination:

Newbury 2009-2010 Q1

But the combinations aren't necessarily grouped together and I don't want to have to sort the data if possible.

[daddylonglegs]

You can use an "array formula", e.g. assume you have the 2 criteria in E2 and F2, use this formula in G2

=MEDIAN(IF(A$2:A$100=E2,IF(B$2:B$100=F2,C$2:C$100)))

confirmed with CTRL+SHIFT+ENTER

[scubadiver007]

The data is in col A, B and C and the criteria are in col F and G, but would it be possible to do it so I don't have to use criteria?

[daddylonglegs]

You could just use each row as the criteria, e.g. in D2 copied down

=MEDIAN(IF(A$2:A$100=A2,IF(B$2:B$100=B2,C$2:C$100)))

Assumes data to row 100, adjust as required

Of course that would give you a result on each row so it will repeat results when each combination is repeated, you could get the MEDIAN against just the first instance of ecah combination by changing to this version

=IF(SUM((A$2:A2=A2)*(B$2:B2=B2))=1,MEDIAN(IF(A$2:A$100=A2,IF(B$2:B$100=B2,C$2:C$100))),"")

both formulas need CTRL+SHIFT+ENTER as before

[scubadiver007]

The first result is what I need because I need the values to repeat, thanks.