Calculating Median's with Multiple Conditions

[dmillerssa]

Hi:

Quick issue I'm hoping someone can help me with.

Since the Pivot function lacks a median calculation, I need to calculate some median's based off three conditions in a data sheet.

I found working formulas for 2 conditions, but not three.

Here is what the data like:

A B C D
1 PUG 2010 January 21.1


D would represent the column I need to calculate the median off off. A, B, C are the conditional columsn.

Any help on this formula?

Thank you!

 

[barry houdini]

Try an "array formula" like this

=MEDIAN(IF((A2:A100=1)*(B2:B100="PUG")*(C2:C100="2010 January"),D2:D100))

confirmed with CTRL+SHIFT+ENTER

You can use cells to contain the criteria if you want.......

 

[pplstuff]

=median(if(and(a:a=1,b:b="pug",c:c="january"),d:d)) <--entered as an array (ctrl +shift+enter)

 

[dmillerssa]

OK I will give these options a try now. I was using a formula similar to what Barry suggested, but perhaps I made an error. I will let you guys know! Thanks.

 

[barry houdini]

=median(if(and(a:a=1,b:b="pug",c:c="january"),d:d)) <--entered as an array (ctrl +shift+enter)

This won't work correctly, pplstuff. You can't use AND in these scenarios because AND returns a single result rather than the required array of results, you have to use * as I did to get the equivalent of AND, or use multiple IFs like this

=MEDIAN(IF(A2:A100=1,IF(B2:B100="PUG",IF(C2:C100="2010 January",D2:D100))))

 

[pplstuff]

AND returns a single result rather than the required array of results

Good call. Sometimes I just get going to fast and don't stop to think, lol. I should probably think more when giving suggestions that people tend to rely on.

 

[dmillerssa]

Hmmm...no luck with either formula. Barry, your formula worked fine on two conditions, but not there.
Here is the formula I'm using: =MEDIAN(IF(('Data-All'!D:D='Analysis-Time & Cost Perform.'!C96)*('Data-All'!T:T='Analysis-Time & Cost Perform.'!C97)*('Data-All'!U:U='Analysis-Time & Cost Perform.'!C98),'Data-All'!W:W)) then of course closing it out w CTRL+ALT-ENTER

 

[pplstuff]

Which criteria did it work for? Maybe your dates are formatted inconsistently?

 

[dmillerssa]

It works for the "2010" and the "January" timeline-but not when I add in this "PUG" value (PS, I built reference cells for all three values, and everything is formatted as 'General').

 

[dmillerssa]

So this works....=MEDIAN(IF(('Data-All'!T:T='Analysis-Time & Cost Perform.'!C97)*('Data-All'!U:U='Analysis-Time & Cost Perform.'!C98),'Data-All'!W:W))

But not when I add the third condition at the front (it comes first in the data-sheet).