Conditional AGGREGATE/Median in Excel 2010

Question

I am currently trying to pull a median from a range of data that has two conditions. Essentially the equivalent of the below AVERAGEIFS(), which I have working fine.

The AVERAGEIFS():

=AVERAGEIFS(Analysis!$F:$F,Analysis!$F:$F,">=0",Analysis!$C:$C,Dashboard!C6,Analysis!$W:$W,Dashboard!B8)

I cannot figure a way to combine MEDIAN and IF(AND( to come up with a similar formula, but think AGGREGATE might be useful!

Any help or sanity checks are appreciated!

Answer 1

It's true that you can't do a conditional median with AGGREGATE function, not directly, but you can easily use function number 16 (PERCENTILE.INC) or function number 17 (QUARTILE.INC) with respectively k values of 0.5 and 2.

These functions allow arrays in AGGREGATE......and have the added advantage of automatically ignoring errors, so you can use this formula for the median with conditions

=AGGREGATE(17,6,Analysis!$F:$F/(Analysis!$C:$C=Dashboard!C6)/(Analysis!$W:$W=Dashboard!B8),2)

Answer 2

You are creating an Array formula with MEDIAN. So a couple of rules when using Array formulas:

1. Do not use full column References in Array type formula. Limit the references to the data set. We can do that automatically with $F$1:INDEX(F:F,MATCH(1E+99,F:F)) this will set the reference in Column F to F1 to the last row with a number in it.

2. AND() does not work in array formulas, either nest IF()s or use * between the Boolean test

3. The formula needs to be confirmed with Ctrl+Shift+Enter instead of Enter when exiting edit mode. If done correctly then Excel will put {} around the formula.

So the formula would be something like this:

=MEDIAN(IF((Analysis!$F$1:INDEX(Analysis!$F:$F,MATCH(1E+99,Analysis!$F:$F))>=0)*(Analysis!$C$1:INDEX(Analysis!$C:$C,MATCH(1E+99,Analysis!$F:$F))=Dashboard!C6)*(Analysis!$W$1:INDEX(Analysis!$W:$W,MATCH(1E+99,Analysis!$F:$F))=Dashboard!B8),Analysis!$F$1:INDEX(Analysis!$F:$F,MATCH(1E+99,Analysis!$F:$F))))