Excel STDEV function estimates the standard deviation of sample values, without evaluating logical and text values in cell references of the sample.
The syntax for Excel STDEV function is:
This function can have arguments between 1 and 255 maximum.
number1 – It is first and required argument
[number2], … – These are optional and additional arguments
Excel STDEV function has been replaced by STDEV.S function in Excel 2010 and later. It is now available for compatibility with Excel 2007 and earlier.
Arguments in Excel STDEV function can be entered as numbers, arrays, named ranges or cell references, that may contain numeric, text, logical values, text representations of numbers and empty cells.
Excel STDEV function evaluates only numeric values in the calculation of standard deviation, ignoring the text, logical values (TRUE and FALSE), text representations of numbers and empty cells in arrays or cell references of a sample set of values. Logical and text representations of numbers supplied directly as the argument in function are evaluated as numbers.
Suppose you a sample of sales figures and the standard deviation is required to be estimated based on this sample dataset, ignoring the text and logical values. This estimate basically tells you that how much monthly sales’ figures deviate from average sales of the sample.
Following formula will be used to calculate a standard deviation of this sample:
Figure 1. STDEV_Function
Please note these points to use Excel STDEV function correctly:
- Excel STDEV function assumes that arguments contain sample data. If data corresponds to population, then use STDEV.P or STDEVP function.
- It does not consider text and logical values in standard deviation estimate of a sample set of values. If these values need to be considered in an evaluation, then use STDEVA function to estimate the standard deviation of the sample.
- It estimates the standard deviation of a sample based on “n-1” method.
- Text representations of numbers and logical values entered directly as arguments in function are evaluated as numbers; otherwise, they are ignored in cell references or arrays.
- It generates #DIV/0! error if all supplied arguments contain text values.
- It generates #VALUE! error if a text value is supplied directly as an argument.