I was goofing around with a spreadsheet and decided to show empirically that the sample standard deviation is an unbiased estimator of the true population standard deviation. Link to Excel File

I started by laying down 4 columns of normally distributed random numbers with a mean of 3.4 and a std dev of 5.6. I used the Excel Data Analysis Toolpack add in for this. Each column has 32000 numbers for a total of 128000 random numbers. Lets call this the population.

I then added a new column that randomly samples from the population by indexing the population range. Every 10 rows in the sampling column, I calculate the sample standard deviation and the population standard deviation. The sampling column extends for 32000 rows. As I calculate the standard deviations every 10 rows, there are 3200 of them.

I then average the sampled standard deviations. I was expecting the average sample standard deviation to be around 5.6, because that is what the true population standard deviation is. In fact, the sample standard deviation is consistently less than 5.6, it is usually around 5.4. Why is this? The sampling uses live formulae so you can refresh by pressing F9.

In the spreadsheet, I have highlighted the true population standard deviation in green and the average sampled standard deviation in yellow.

  • $\begingroup$ You might be interested in this and this. $\endgroup$ – angryavian Feb 14 '14 at 14:57
  • 1
    $\begingroup$ ok, ive got the answer from your second link: its because the sample standard deviation is not an unbiased estimator but the sample variance is. Running through the same procedure with variance yields the correct result. $\endgroup$ – Chechy Levas Feb 14 '14 at 15:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.