Related Function:

The STDEV.S function calculates the sample standard deviation of a supplied set of values.

  • This function is new in Excel 2010 and so is not available in earlier versions of Excel. However, the STDEV.S function is simply a new version of the STDEV function that is available in earlier versions of Excel.

Syntax

=STDEV.S(num_1,[num_2], …)

Arguments

Argument Description
num_1 The first number argument corresponding to a sample of a population
[num_2], … Optional. Additional number arguments 2 to 254 corresponding to a sample of a population

Note: Arguments to the STDEV.S function can be input as cell ranges, individual numbers, or number arrays, e.g., in the table below:

  • The result in J3 uses a range of cells (see K3)
  • The result in J4 uses cell ranges plus individual numbers (see K4)
  • The result in J5 uses cell ranges plus a number array (see K5)

Examples

  A B C D E F G H I J K
1 2010 2011 2012 2013   Standard
Deviation:
 
2 Month Amount Month Amount Month Amount Month Amount    Formula used
3 Jan-10 15,000 Jan-11 17,500 Jan-12 13,000 Jan-13 13,000   2,519.29 =STDEV.S(B3:B14,D3:D14,F3:F14)
4 Feb-10 14,500 Feb-11 12,000 Feb-12 15,000 Feb-13 14,500   2,468.04 =STDEV.S(B3:B14,D3:D14,F3:F14,13000,14500)
5 Mar-10 14,500 Mar-11 16,000 Mar-12 14,000       2,468.04 =STDEV.S(B3:B14,D3:D14,F3:F14,{13000,14500})
6 Apr-10 14,000 Apr-11 19,000 Apr-12 16,500          
7 May-10 16,000 May-11 17,000 May-12 20,000          
8 Jun-10 9,500 Jun-11 10,500 Jun-12 12,500          
9 Jul-10 13,500 Jul-11 11,000 Jul-12 14,000          
10 Aug-10 17,000 Aug-11 12,500 Aug-12 18,500          
11 Sep-10 11,000 Sep-11 13,000 Sep-12 14,500          
12 Oct-10 15,000 Oct-11 15,500 Oct-12 13,000          
13 Nov-10 17,500 Nov-11 15,000 Nov-12 13,000          
14 Dec-10 18,000 Dec-11 17,500 Dec-12 17,000          

Usage note:

  • The STDEV.S function is used when calculating the standard deviation for a sample of a population. If you are calculating the standard deviation of an entire population, you need to use the STDEV.P function.
  • The STDEV.S function ignores text values and logical values if these are supplied as part of an array. If you want a function that includes text and logical values in the calculation, consider using the STDEVA function.

Common Function Error(s)

Problem What went wrong
#VALUE! Occurs if any values that are supplied directly to the function are text values that cannot be interpreted as numeric values
#DIV/0! Occurs if fewer than two numeric values are supplied to the function

  Text representations of numbers, supplied as a part of an array, are not interpreted as numeric values

The Standard Deviation is a statistical measure, that is closely linked to the Variance. Both measures are commonly used across a set of values, to identify the amount that the values differ, or deviate, from the average value.

When your data set is a sample of a population, rather than an entire population, you should use a slightly modified form of the Standard Deviation, known as the Sample Standard Deviation. The equation for this is:

    \[ \text{Sample Standard Deviation} = \sqrt{ \sum \frac{(x- \bar{x})^2}{(n-1)}  \]

where,

  • x takes on each value in the set
  • x is the average, statistical mean, of the set of values
  • n is the number of values.

A full explanation can be found on the Wikipedia Standard Deviation page

.