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

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

I have a list of numbers and i want to calculate avg and stdev but i do not want "zeros" or "blanks" to enter into the calculations. I have a formula for avg but i need one for stdev.

**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?