ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel
  • »
  • Business and Employment»
  • Learn Business Skills

Microsoft Excel Standard Deviation: =STDEV vs =STDEVP

Updated on May 26, 2015

Before using Excel's standard deviation function it is helpful to review what standard deviation is. Standard deviation is a very useful number in both business and statistical occupations.

Standard Deviation: Average distance of a point from the mean.

Population Standard Deviation

Population: A set of data that is all inclusive.

Populations are often very large. For simplicity, lets imagine the following sample:

12,6,12

In this case it is simple to figure the standard deviation.

1. Determine the mean of the sample

12+6+12/3=10

2. Square the distance between each point and the mean

(12-10)^2 =4

(6-10)^2 =16

(12-10)^2 =4

3. Calculate of the average distances

4+16+4/3=24/3 or √8


Standard Deviation of a Sample

Sample: An smaller set of data used to represent a large population.

Bessel's Correction: size of sample equals (n-1). This increases the standard deviation by estimates the error associated with the sample when compare to the entire population. Adding an additional data point will always increase the standard deviation unless the point falls on the mean. i.e. If a sample is missing data points included in the population the standard deviation will likely be lower.

Consider the same example only this time are data is sample:

1. Determine the mean of the sample

12+6+12/2=15

2. Square the distance between each point and the mean

(12-10)^2 =4

(6-10)^2 =16

(12-10)^2 =4

3. Calculate of the average distances

4+16+4/2=24/2 or √12


Standard Deviation in the Real World

The confidence interval expressed earlier are only relevant on large sets of data. These set of data are often tens, hundreds, thousands, or even more points. Luckily for us excel has included formulas that allow us to quickly calculate the standard deviation on populations and samples.


Standard Deviation of a Population in Excel

Let's imagine we have the stock returns of GE from 1963 to present and this represents the entire population of data available.

In Excel for stand deviation of a population use the following formula =STDEVP(H2:H51) See below

The standard can then be express in terms of confidency

We are 68.2% confident the return will fall between

The mean (13.109%) +/- one standard deviation (25.053%) or between -12% and 38%

We are 95.5% confident the return will fall between

The mean (13.109%) +/- two standard deviations (50.106%) or between -37% and 63%

We are 99.7% confident the return will fall between

The mean (13.109%) +/- three standard deviations (75.159%) or between -62% and 88%


Standard Deviation of a Sample in Excel

Now that assume we are going to only use the past 10 year's returns as a sample of the population.

For this calculation we will use the formula =stdev(H2:H12)

This formula tells excel to use Bessel's Correction (n-1) rather than n in the calculation. This will adjust the variance up in an attempt to account for additional data points in the population not included in the sample.

Comments

    0 of 8192 characters used
    Post Comment

    • Margaret Skipper profile image

      Margaret Skipper 4 years ago from Baton Rouge, LA

      Wow, you've made it much easier to understand! Great hub... thanks for writing it!

    • profile image

      hey 20 months ago

      hey whats up

    • profile image

      Beers 17 months ago

      "1. Determine the mean of the sample

      12+6+12/2=15"

      How can 15 possibly be construed as the "mean" [12,6,12]? Why only divide by 2 and not 3?

    Click to Rate This Article