How to calc stdev but to exclude 0's. Prefer no array cntrl + Shift + enter calcs

**shg** · 12-12-2008, 06:37 PM

If you have a formula that works for AVERAGE, just change AVERAGE to STDEV.

**welchs101** · 12-12-2008, 06:45 PM

formula for avg:
SUM(A1:A5)/COUNTIF(A1:A5,">0")

Cant just replace avg with stdev

**shg** · 12-12-2008, 06:52 PM

=stdev(if(a1:a5>0, a1:a5))

That is an array formula, though.

**welchs101** · 12-12-2008, 06:54 PM

Yeah, its hard to find a formula with out using the array calc.

**shg** · 12-12-2008, 06:56 PM

You could do it with a helper column: =if(a1>0, (a1-avg)^2, ""), where avg is the value you computed with your other formula.

Then St Dev =sqrt(sum(B1:B5) / (count(b1:b5) - 1) )

Why the aversion to array formulas?

**welchs101** · 12-12-2008, 07:11 PM

I have used array formulas in the past and they mess me up sometimes. I also remember reading somewhere that there are certain rules you must follow so you dont get an error when using array formulas......do you know what they are?