Cond STDEV-formula seems wrong {=STDEV(IF(E1:E8=1,G1:G8,0))}

[Jorgen]

I read some other threads about conditional Standard Deviation-calculations using the formula {=STDEV(IF(E1:E8=1,G1:G8,0))} (being entered with ctrl-shift enter) but I get the wrong results using it. And as I was checking for this post - I found out that AVERAGE does the same.
I tested with these values in C8:C27 (Actually created from Normal distribution 10, 5 and 100, 50 as a hint of what the results should be)

1 12.7021177
1 10.41939925
1 11.00331069
1 7.226683093
1 7.405969917
1 13.77274362
1 -0.983961511
1 13.54316044
1 14.47282228
1 12.7528812
2 127.021177
2 104.1939925
2 110.0331069
2 72.26683093
2 74.05969917
2 137.7274362
2 -9.839615114
2 135.4316044
2 144.7282228
2 127.528812

Using {=AVERAGE(IF(C8:C27=1,D8:D27,0))}
and {=STDEV(IF(C8:C27=1,D8:D27,0))} I end up with
1-> Average=5.116 Stdev=6.15932
2-> Average=51.158 Stdev=61.59319
which is wrong since if I hardcode the ranges for the 1:s and 2:s (like =STDEV(D8:D17)) I get

1-> Average=10.2315 Stdev=4.68316
2-> Average=102.315 Stdev=46.8316

So since the conditional formula above doesn´t work my question that this has been leading up to is:
How can I do conditional Standard Deviation and get a correct result in Excel?

Thanks,

Jörgen

 

[PaddyD]

re-check the original posts, then consider the difference between:

=STDEV(IF(C8:C27=1,D8:D27,0))

and

=STDEV(IF(C8:C27=1,D8:D27))

...the latter is what you need.

 

[Jorgen]

Thanks! When put like that, it makes sense...