How to Estimate Standard Deviation With Excel's STDEV Function

of 01

Excel STDEV (Standard Deviation) Function

Estimating Standard Deviation with the STDEV Function in Excel 2007
Estimating Standard Deviation with the STDEV Function. © Ted French

A standard deviation is a statistical tool that tells you roughly how far, on average, each number in a list of data values varies from the average value or arithmetic mean of the list itself.

For example, for the numbers 1, 2

  • the average value is 1.5 (calculated by adding the two numbers together and dividing by 2: (1+2)/2)
  • the standard deviation or distance away from this mean of 1.5 for the numbers 1 and 2 is 0.5 - since each number is either 0.5 greater or lesser in value than 1.5

The STDEV function, however, gives only an estimate of the standard deviation. The function assumes that the entered numbers represent only a small portion or sample of the total population being studied.

As a result, the STDEV function does not return the exact standard deviation. For example, for the numbers 1, 2 the STDEV function in Excel returns an estimated value of 0.71 rather than the exact standard deviation of 0.5.

STDEV Function Uses

Even though it only estimates the standard deviation, the function still has its uses when only a small part of a total population is being tested.

For example, when testing manufactured products for conformity to the mean - for such measures as size or durability - not every unit is tested. Only a certain number are tested and from this an estimate of how much each unit in the entire population varies from the mean can be gained using STDEV.

To show how close the results for STDEV can be to the actual standard deviation, in the image above, the sample size used for the function was less than one-third of the total amount of data yet the difference between the estimated and actual standard deviation is only 0.02.

The  STDEV Function's Syntax and Arguments

A function's syntax refers to the layout of the function and includes the function's name, brackets, comma separators, and arguments.

The syntax for the Standard Deviation function is:

= STDEV ( Number1, Number2, ... Number255)

Number1 - (required) - can be actual numbers, a named range or cell references to the location of data in a worksheet.
- if cell references are used, empty cells, Boolean values, text data, or error values in the range of cell references are ignored.

Number2, ... Number255 - (optional) - up to 255 numbers can be entered

Example Using Excel's STDEV

In the image above, the STDEV function is used to estimate the standard deviation for the data in cells A1 to D10.

The sample of data used for the function's Number argument is located in cells A5 to D7.

For comparison purposes, the standard deviation and the average for the complete data range A1 to D10 is included

The information below cover the steps used to enter the STDEV function in cell D12.

Entering the STDEV Function

Options for entering the function and its arguments include:

  1. Typing the complete function: =STDEV(A5:D7) into cell D12
  2. Selecting the function and its arguments using the STDEV function dialog box

Although it is possible to just type the complete function in by hand, many people find it easier to use the dialog box to enter a function's arguments.

Note, the dialog box for this function is not available in Excel 2010 and later versions of the program. To use it in these versions, the function must be entered manually.

The steps below cover using the function's dialog box to enter STDEV and its arguments into cell D12 using Excel 2007.

Estimating the Standard Deviation

  1. Click on cell D12 to make it the active cell - the location where the results for the STDEV function will be displayed
  2. Click on the Formulas tab.
  3. Choose More Functions > Statistical from the ribbon to open the function drop down list.
  4. Click on STDEV in the list to bring up the function's dialog box.
  5. Highlight cells  A5 to D7 in the worksheet to enter the range into the dialog box as the Number argument
  6. Click OK to close the dialog box and return to the worksheet.
  7. The answer 2.37 should present in cell D12.
  8. This number represents the estimated standard deviation of each number in the list from the average value of 4.5
  9. When you click on cell E8 the complete function =STDEV(A5:D7) appears in the formula bar above the worksheet

Reasons for Using the Dialog Box Method Include:

  1. The dialog box takes care of the function's syntax - making it easier to enter the function's arguments one at a time without having to enter the equal sign, the brackets, or the commas that act as separators between the arguments.
  2. Cell references can be entered into the formula using pointing, which involves clicking on selected cells with the mouse rather than typing them in. Not only is pointing easier, it also helps to reduce errors in formulas caused by incorrect cell references.