Ask Your Question
0

Understanding standard deviation

asked 2015-10-14 06:24:42 +0200

Capum130 gravatar image

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))

see also: a link b link

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2017-03-07 04:49:57 +0200

bert munger gravatar image

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.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2015-10-14 06:24:42 +0200

Seen: 1,115 times

Last updated: Mar 07 '17