# Understanding standard deviation

hello

To increase my knowledge about stdev first: what happens to the result when values, equal to zero in the range (column), are included in calculating this range' stdev ?

Use as example a spreadsheet of money prize of a lottery. I calculated the average, the highest and lowest output of full year span of winnings. Some contests didn't have winner, so this zero is related to my first paragraph. Then I am trying to read this amount in stdev cell, which it's bigger(1) than the average output. How I comprehend is the stdev + average = the higher dispersion; and average - stdev = the lower dispersion, which is negative because of (1)!?
How is it possible? Why it gets bigger than average?

here are my stdev cell write out:
=stdev(if(F2:F1879<>0; F2:F1879))

and, with different result, might be incorrect:
=stdev(if(countif(F2:F1879;"<>0");F2:F1879))

edit retag close merge delete

Sort by » oldest newest most voted Standard deviation is the square root of the sum of the squared deviations from the mean.

Sigma = sqrt(sum(X-mean)^2)

Zero doesn't have any special significance.

If you put the numbers 1 through 12 in a column and have a last entry of 13,000,000 you will see that it is indeed possible for the standard deviation to be larger than the mean.

I'll bet (pardon the pun) that lotteries pay out a lot of relatively small prizes and rarely have a big payoff. This would result in a small average and a large standard deviation.

more