Standard deviation is a number that tells you how far numbers are from their mean.

There are basically two type of standard deviation that is being used in excel:

  • Standard deviation based on samples (STDEV.S).
  • Standard deviation based on population (STDEV.P).

► Play

Standard deviation based on Samples (STDEV.S)

The STDEV.S function (the S stands for Sample) in Excel estimates the standard deviation based on a sample. The STDEV.S function uses the following formula:

Where,

Now let’s construct an example, imagine there was a class test of 10 students and we take 5 samples out of the entire 10 students to check standard deviation. The class tests marks of those 5 students are 7, 9, 12, 8, and 14. Now firstly we will use the formula and then we will verify the same in excel without using any formula.

  • In a new workbook of excel, write marks of sample students under any heading. You will get something like this
  • Now in cell A8 we will use our formula, to use formula on my worksheet I will use :-
    =STDEV.S(A2:A6)
  • Now we will manually verify the formula used. For this we will repeat the first step in sheet 2.
  • Now in cell A8, we will find the average of sample by using the following formula:-
    =AVERAGE(A2:A6)
  • In column B we will find xi – x , for this we will subtract marks from average. And in the end we will get a product like this:
  • Now multiply the difference with itself to get the squared value of difference and then have a sum of such value
  • Now in next cell we will complete the formula that is dividing the sum of square of difference that is 34 with no of sample minus one that is 5-1 = 4 and then square root of any value that come from such division.   

Tips:

  • Steps from 3 to 7 are just for manually checking whether formula works fine or not. You need not to repeat step3 to step 7.
  • In office 2007 or earlier versions, STDEV was used. STDEV will deliver the same result as STDEV.S

Standard deviation based on population (STDEV.P)

The STDEV.P function (the P stands for Population) in Excel calculates the standard deviation based on the entire population. The STDEV.P function uses the following formula:

Where,

Now let’s construct an example, imagine there was a class test of 5 students. The class tests marks of those 5 students are 7, 9, 12, 8, and 14. Now firstly we will use the formula and then we will verify the same in excel without using any formula.

  • In a new workbook of excel, write marks of all students under any heading. You will get something like this:
  • Now in cell A8 we will use our formula, to use formula on my worksheet I will use :-
    =STDEV.P(A2:A6)
  • Now we will manually verify the formula used. For this we will repeat the first step in sheet 2
  • Now in cell A8, we will find the average of sample by using the following formula:-
    =AVERAGE(A2:A6)
  • In column B we will find xi – u, for this we will subtract marks from average. And in the end we will get a product like this:
  • Now multiply the difference with itself to get the squared value of difference and then have a sum of such value
  • Now in next cell we will complete the formula that is dividing the sum of square of difference that is 34 with total population and then square root of any value that come from such division.

Tips:

  • Steps from 3 to 7 are just for manually checking whether formula works fine or not. You need not to repeat step3 to step 7.
  • In office 2007 or earlier versions, STDEVP was used. STDEVP will deliver the same result as STDEV.P

Send us a feedback0/500

Do you like this article?
Yes
No