What Is the STDEV Function in Excel?
In Microsoft Excel, the STDEV function and the STDEV.S function are both tools to help you estimate the standard deviation based on a set of sample data. A standard deviation is a statistical tool that tells you roughly how far each number in a list of data values varies from the average value or arithmetic mean of the list itself. In Excel 2010 and later, the STDEV.S function is used, replacing the STDEV function that is part of older versions. STDEV is considered to be a “compatibility function,” meaning it can still be used in later versions of Excel to ensure backward compatibility. Still, Microsoft recommends you use the newer function when backward compatibility isn’t required.
Practical Uses of the STDEV Function
The STDEV and STDEV.S functions provide an estimate of a set of data’s standard deviation. The function assumes that the entered numbers represent only a small sample of the total population being studied. As a result, it doesn’t return the exact standard deviation. For example, for the numbers 1 and 2, the STDEV function in Excel returns an estimated value of 0.71 rather than the exact standard deviation of 0.5. STDEV and STDEV.S are useful when only a small part of a total population is being tested. For example, if you’re testing manufactured products for conformity to the mean (for measures such as size or durability), you can’t test every unit, but you’ll get an estimate of how much each unit in the entire population varies from the mean. To show how close the results for STDEV are to the actual standard deviation (using the example above), the sample size used for the function was less than one-third of the total amount of data. The difference between the estimated and the actual standard deviation is 0.02.
STDEV and STDEV.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 these functions is as follows:
STDEV
STDEV(number1,[number2],…) Number1 is required. This number 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, … is optional. These number arguments correspond to a sample of the population. You can also use a single array or a reference to an array instead of arguments separated by commas.
STDEV.S
STDEV.S(number1,[number2],…) Number1 is required. The first number argument corresponds to a sample of a population. You can also use a single array or a reference to an array instead of arguments separated by commas. Number2, … is optional. Number arguments 2 to 254 correspond to a sample of a population.
Example of the STDEV Function
For this tutorial, the sample of data used for the function’s Number argument is located in cells A5 to D7. The standard deviation for this data will be calculated. For comparison purposes, the standard deviation and the average for the complete data range A1 to D10 are included. In Excel 2010 and Excel 2007, the formula must be entered manually. Follow these steps to complete the task and calculate the information using the built-in function: =STDEV(A5:D7)
