Standard Deviation and Averages based on criteria

[benzeb666]

I need to do the standard deviation and averages of some numbers base
on some criteria that is in the same row as the number. The criteria ca
be contained in any one of three designated cells, and if one criteri
is met it should include the corresponding number value in the standar
deviation and average calculations. The problem is if it meets with tw
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 an
nothing has worked.

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

 

[benzeb666]

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

 

[joeu2004]

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))

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="rubber"),E4:E8))

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