stdev IF...

I am having trouble figuring out a way to calulate the stdev of certain values.
so- what is the stdev of values in column E if the values in column A ="A"?

essentally I need stdev to function like SUMIF.

Im attaching my data.

Also, is there a function that will return the average IF. I am currently summing If, and then dividing by the countIF. is there a function that does the same thing?



Unrelated question:
what exactly does ctrl-shift-enter do?

Hi

You can use an array formula, something like this:

=STDEV(IF($A$2:$A$200="A",$B$2:$B$200))
or
=STDEV(IF($A$2:$A$200=A2,$B$2:$B$200))

enter using CTRL+SHIFT+ENTER....after you hit ENTER your formula should look like this {=STDEV(IF($A$2:$A$200=A2,$B$2:$B$200))} the brackets means that it is an array formula.

you can do the same with the average formula. As for the ctrl+shift+enter, you use those key combination to enter an array formula, I'm not an expert so I won't even try to explain it I'll leave that to the experts.

Hope this helps
Jean-Guy

works like a charm

Thank you

Hi Joshua

If you're interested, here is a non repeating solution:

=IF(A2=A3,"",STDEV(IF($A$2:$A$200=A2,$B$2:$B$200)))

will only calculate on the last instance of a letter in column A

or

=IF(A2=A1,"",STDEV(IF($A$2:$A$200=A2,$B$2:$B$200)))

will only calculate on the first instance of a letter in column A

again enter using CTRL+SHIFT+ENTER

Cheers
Jean-Guy