Standard Deviation w/ multiple criteria...

[MeatLightning]

Ok... this might be impossible or otherwise crazy... but I'll ask anyway:

Is there a way to calculate standard deviation where only certain qualifying
data is analyzed?

I have a bunch of data... For example:

orderID Order $ Date # of Parts
w1234 $5 1/1/09 10
w1235 $10 1/1/09 5
w1236 $7 1/4/09 10

etc, etc (I have like 9k rows and my real data has more columns)

Currently I chew through this data using SUMPRODUCT to pull together
different groups. For example:
- Show me total order $ for "w1234" in 2009.
- Show me # of orders for "w1234" in 2009.
- Show me Avg. order $ for "w1234" in 2009.

My question is: Is there a way to calculate standard deviation in a similar
way? For example: Show me standard deviation of order $ for "w1234" in 2009.

I'm trying to avoid manually copying the qualifying entries into their own
sheet / area or using a macro. SUMPRODUCT kicks butt for this kind of thing
because you can just string together all sorts of criteria to analyze a pile
of data without moving or editing the pile.

I see that STDEVA accepts up to 255 unique references... but this would
require manually selected each cell... I want to give it parameters and let
it find the qualifying cells (in a given column of course) on it's own.

Any ideas?

 

[smartin]

Ok... this might be impossible or otherwise crazy... but I'll ask anyway:

Is there a way to calculate standard deviation where only certain qualifying
data is analyzed?

I have a bunch of data... For example:

orderID Order $ Date # of Parts
w1234 $5 1/1/09 10
w1235 $10 1/1/09 5
w1236 $7 1/4/09 10

etc, etc (I have like 9k rows and my real data has more columns)

Currently I chew through this data using SUMPRODUCT to pull together
different groups. For example:
- Show me total order $ for "w1234" in 2009.
- Show me # of orders for "w1234" in 2009.
- Show me Avg. order $ for "w1234" in 2009.

My question is: Is there a way to calculate standard deviation in a similar
way? For example: Show me standard deviation of order $ for "w1234" in 2009.

Agreed SUMPRODUCT is great for sifting through multiple criteria, but
only to produce counts and sums.

Array formulae can take care of all of your needs in this case:
=STDEV(IF(($A$2:$A$11="w1234")*(YEAR($C$2:$C$11)=2009),$B$2:$B$11))

Array formulae need to be committed by pressing Ctrl+Shift+Enter (not
just enter).

Substitute SUM, COUNT, or AVERAGE for the other three metrics. I'm
guessing with 9k rows this shouldn't be too slow.

 

[MeatLightning]

cool thanks!

Agreed SUMPRODUCT is great for sifting through multiple criteria, but
only to produce counts and sums.

Array formulae can take care of all of your needs in this case:
=STDEV(IF(($A$2:$A$11="w1234")*(YEAR($C$2:$C$11)=2009),$B$2:$B$11))

Array formulae need to be committed by pressing Ctrl+Shift+Enter (not
just enter).

Substitute SUM, COUNT, or AVERAGE for the other three metrics. I'm
guessing with 9k rows this shouldn't be too slow.