Excel STDEV.P function returns standard deviation of population data set, ignoring logical values and text.
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.
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.
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:
Figure 1. STDEV.P_Function
- 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.