Standard Deviation and Averages based on criteria
 

I need to do the standard deviation and averages of some numbers based on some criteria that is in the same row as the number. The criteria can be contained in any one of three designated cells, and if one criteria is met it should include the corresponding number value in the standard deviation and average calculations. The problem is if it meets with two criteria it is adding the value in twice to calculate it.

CRIT1..|..CRIT2..|..CRIT3..|..VAL..|..AVG(RR)..|.. STDEV(RR)
red......|..plastic.|............|...5....|....... ........|0.816496581
blue.....|..plastic.|............|...3
green...|..plastic.|............|...3
green...|..rubber.|............|...3
red......|..rubber..|...........|...4

The formula I am trying is:
=STDEVA(IF(B4:B8="red", E4:E8), IF(C4:C8="rubber", E4:E8))

The real sample standard deviation for what I want is 1 (first, fourth, and fifth items should be used). I have tried many different ways and nothing has worked.

Also, is there a way to exclude the value in the calculation if one of the criteria is a specific thing? Such as, excluding green but including rubber, I would not want the fourth item in the calculation. Thanks for reading this!

I have developed a PHP script that will generate the formula for infinite number of categorical variables. If anyone is interested let me know.

Standard Deviation and Averages based on criteria
 

"benzeb666" wrote:
I need to do the standard deviation and averages of some
numbers [...]. The criteria can be contained in any one
of three designated cells, and if one criteria is met it
should include the corresponding number value in the standard
deviation and average calculations.
[....]
The formula I am trying is:
=STDEVA(IF(B4:B8="red", E4:E8), IF(C4:C8="rubber", E4:E8))

Generally, use STDEV, not STDEVA. Array-enter the following formulas (press
ctrl+shift+Enter instead of just Enter):

=STDEV(IF((B4:B8="red")+(C4:C8="rubber"),E4:E8))

=AVERAGE(IF((B4:B8="red")+(C4:C8="rubber"),E4:E8))


"benzeb666" wrote:
Also, is there a way to exclude the value in the calculation
if one of the criteria is a specific thing? Such as,
excluding green but including rubber, I would not want the
fourth item in the calculation.

Again, array-enter the following formulas:

=STDEV(IF((B4:B8="red")+(B4:B8<"green")*(C4:C8="r ubber"),E4:E8))

=AVERAGE(IF((B4:B8="red")+(B4:B8<"green")*(C4:C8= "rubber"),E4:E8))