STDEV + IF

Question

I am trying to get the standard deviation of a set of values based on a condition. I have a number of reaction times for a picture memory test, and I want to get the standard deviation for the items that were previously shown (I.e. that they should have remembered). These values are labeled as 'y' (i.e. the pictures have been shown previously) and 'n' (i.e. new pictures, not shown previously). I have gotten the mean value for the previously shown pictures using the following formula: 

=AVERAGEIF($AU$115:$AU$226,"y",$BG$115:$BG$226)

To get the standard deviation, I have tried:

=STDEV(IF($AU$115:$AU$226,"y",$BG$115:$BG$226))

but I keep getting a value error. I have also tried to set the IF range to ='y', but it is not working. I have also tried enter+ctrl+shift, and that does not work either. If anyone could help, I would greatly appreciate it!

Answer 1

Your formula looks fine. I suspect you failed to array enter it with Ctrl+Shift+Enter rather then just Enter.

In the snip below, the yellow is what we wish to do, the green is there to show the array formula does work

Type =STDEV.S(IF(B2:B11="y",A2:A11)) then press Ctrl+Shift+Enter ; Excels will add the curly braces (you must not type them)

best wishes

Answer 2

Thanks- this worked great! I don't suppose you could also help with multiple IFs...

Now I am trying to get the SD for reaction time of correct responses within a certain group. Based off the first formula, I would suspect it would look something like this (but of course I cannot get it to work):

=STDEV.S(IF($DO$115:$DO$226="CR",$AZ$115:$AZ$226=1,$BG$115:$BG$226))

Answer 3

Is your condition that DO="CR" ANDAZ=1? If so I suspect you need to change it to

=STDEV.S(IF(AND($DO$115:$DO$226="CR",$AZ$115:$AZ$226=1),$BG$115:$BG$226))

Remembering of course to CTRL+SHIFT+ENTER to make it an array formula.

Answer 4

Thanks- I finally got it to work! I appreciate the help.