Excel STDEV Function

Excel STDEV function estimates the standard deviation of sample values, without evaluating logical and text values in cell references of the sample.

Formula

The syntax for Excel STDEV function is:

=STDEV(number1,[number2],...)

 

This function can have arguments between 1 and 255 maximum.

number1 – It is first and required argument

[number2], … – These are optional and additional arguments

Explanation

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.

Example

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:

=STDEV(B2:B13)

Figure 1. STDEV_Function

Notes

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.
Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar