Standard Deviation Formula that will ignore 0 ,,,,, help...

[stephen.smith]

Hi guys I am having serious problems with a very large database I need to use. I am trying to calculate standard deviation but my results are being thrown by 0s in the data, is there a formula that ignores the 0 and still calculates the standard deviation, I also need to do this for mean formula aswell, any help would be greatly appreciated.

 

[baitmaster]

welcome to the board

please post your existing formulas, it will be easier to update them once we can see what is happening exactly

 

[stephen.smith]

=STDEV(G41,G40,G39,G38,G37,G36,G35,G34,G33,G32,G30,G31,G29,G28,G27,G26,G25,G24,G23,G22,G21,G20,G19,G18,G17,G16,G15,G14,G13,G12,G11,G10,G9,G8,G7,G6,G5,G4,G3,G2,G2)
This is the formula I am using. All of the info that comes into these cells is coming from a 40 questionnaires that are filled out on a weekly basis by different employees in different areas so if a question is not relevant it is not answered and then a value of 0 is left in the corresponding cell and thus this is messing up our results.
Thanks again for the help

 

[barry houdini]

You have G2 in there twice is that deliberate? for a range, i.e. G2:G41 you can use this array formula

=STDEV(IF(G2:G41<>0,G2:G41))

confirmed with CTRL+SHIFT+ENTER

You can do the same with AVERAGE or if you have Excel 2007 you can use AVERAGEIF function

 

[baitmaster]

try

=STDEV(IF(G1:G41<>0,G1:G41))

using shift + ctrl + enter to confirm, as this is an array formula

note, I assume that the duplication of G2 is an error, and I have extended your range to G1 instead

 

[stephen.smith]

Thanks guys that was really helpful.

 

[CharlesLester]

Hmm, both this: =STDEV.P($B11:$NC11)
and this: =STDEV.P(IF($B11:$NC11<>0,$B11:$NC11,0)) (ctrl+shift+enter)
give me the same result, namely 0.052199 (of the 366 entries, 365 are 0 and one is 1, which should give me a standard deviation of 0). It doesn't appear that this solution is ignoring the zero entries.

I am using Excel 2010.

 

[CharlesLester]

Ah. =STDEV.P(IF($B14:$NC14<>0,$B14:$NC14," ")) is the correct formula

 

[Aladin Akyurek]

Hmm, both this: =STDEV.P($B11:$NC11)
and this: =STDEV.P(IF($B11:$NC11<>0,$B11:$NC11,0)) (ctrl+shift+enter)
give me the same result, namely 0.052199 (of the 366 entries, 365 are 0 and one is 1, which should give me a standard deviation of 0). It doesn't appear that this solution is ignoring the zero entries.

I am using Excel 2010.

Control+shift+enter, not just enter:

=STDEV.P(IF(1-($B11:$NA11=""),$B11:$NA11))

If real zero's and empty cells must be ignored...

=STDEV.P(IF(ISNUMBER(1/$B11:$NA11),IF($B11:$NA11>0,$B11:$NA11)))