< Go Back

Excel STDEV.P Function

Excel STDEV.P function returns standard deviation of population data set, ignoring logical values and text.

Formula

The syntax for Excel STDEV.P function is:

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

The function has total 255 arguments.

number1: It is required and the first argument.

[number2], ….): These are optional and between 2 to 255 arguments.

Explanation

Excel STDEV.P function is introduced in Excel 2010 to replace Excel STDEVP function and is not available in earlier versions. STDEVP function is still available for compatibility with Excel 2007 and earlier versions. Arguments of Excel STDEV.P function are numeric values, named ranges, arrays of values or cell references that may contain numbers, logical values, text values, empty cells, and text representations of numbers.

Excel STDEV.P function evaluates numeric values only to calculate standard deviation, ignoring the text, logical value, empty cells and text representations of numbers available in cell references, ranges or arrays as arguments.

Logical values and text representations of numbers added directly as arguments in Excel STDEV.P function are evaluated as numbers and counted in standard deviation calculation.  

Example

Suppose you have data set of last three years’ sales of your company and you need to see how much of three years’ sales figures are widely dispersed from average sales of three years. So, you need to calculate the standard deviation of the entire population, ignoring the text, logical values, and empty cells, if found in cell references of data set.

As each year’s sales figures are available in the range of cells, so you need to supply three ranges of cells in Excel STDEV.P function in the following formula:

=STDEV.P(B2:B13,C2:C13,D2:D13)

Figure 1. STDEV.P_Function

Notes

  • Excel STDEV.P function assumes that data supplied in arguments belong to the entire population instead of a sample of the population. If the data set is a sample then use Excel STDEV.S or STDEV function.
  • It evaluates only numbers ignoring logical and text values in cell references. If you need to evaluate logical and text values, then use STDEVPA function.
  • It uses the “n” method to calculate the standard deviation of the population.
  • Text representations of numbers and logical values are evaluated as numbers if added as a direct argument in the function.
  • If none of the values are numbers in cell references, the function returns #DIV/0! error.
  • If any of values supplied directly as an argument is a text value, it returns #VALUE! error.
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