Ask Your Question
0

Understanding standard deviation [closed]

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

Capum130 gravatar image

updated 2020-09-26 23:48:47 +0100

Alex Kemp 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 reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-09-26 23:49:08.336912

1 Answer

Sort by » oldest newest most voted
0

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

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

Question Tools

1 follower

Stats

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

Seen: 2,642 times

Last updated: Mar 07 '17