STDEVP Function in Google Sheets – Standard Deviation

GiGiKing·Tháng ba 29, 2024·0 comments

To calculate the standard deviation of an entire population (σ), not a sample, we can use the STDEVP or STDEV.P function in Google Sheets.

Earlier, we discussed the use of the STDEV (STDEV.S) function, which is used for sample data, not an entire population. It’s important to understand the difference between the two.

In Google Sheets, we have two functions for calculating the standard deviation: STDEVP and STDEV.P. STDEVP is used for finding the standard deviation of an entire population, while STDEV.P is used for finding the standard deviation of a sample.

I recommend using STDEV.P for samples and STDEVP for entire populations. These functions are relatively newer in Google Sheets and their names, especially the [dot]S and [dot]P, better reflect their usage.

At the time of writing this article, all of these functions are working fine in Google Sheets and I believe they will continue to do so for compatibility purposes.

Inserting Standard Deviation Symbol in Google Sheets

In Google Sheets, the standard deviation is represented by the Greek letter sigma (lowercase). The symbol is σ.

To insert the standard deviation symbol in Google Sheets, you can use the following formula:

=char(963)

In the above formula, the argument 963 is the Unicode table number to insert the lowercase sigma symbol in Google Sheets.

Standard Deviation of an Entire Population – Manual Calculation in Sheets

Before we dive into the STDEVP function in Google Sheets, let’s understand how to manually calculate the standard deviation of an entire population.

In statistics, the standard deviation is a measure of the amount of variation in a set of values, indicating how spread out the values are.

Here are the steps to manually calculate the standard deviation without using the STDEVP function:

Step 1: Finding the Mean (Average)

To find the mean, add up all the values in the set and divide by the total number of values.

Step 2: Find the Difference of Each Value from the Mean, then Square it

Subtract the mean from each value in the set, square the result, and add up all the squared differences.

Step 3: Average the Sum of Squared Differences, which is the Variance (σ^2)

Divide the sum of squared differences by the total number of values.

Finally, find the square root of the variance to get the standard deviation.

We can avoid all these manual calculations by using the STDEVP or STDEV.P function in Google Sheets.

STDEVP / STDEV.P Function Syntax

The syntax for the STDEVP and STDEV.P functions is as follows:

STDEVP(value1, [value2, ...])
STDEV.P(value1, [value2, ...])

Arguments:

value1 – The first value or array/range of the population.
value2 – Additional values or arrays/ranges to include in the population.

Let’s see an example using the same values used in our manual calculation above for a better understanding:

The numbers are in the range B3:B7. Here are the STDEVP and STDEV.P formulas:

=STDEVP(B3:B7)
=STDEV.P(B3:B7)

STDEVP Function in Google Sheets - Example

As mentioned earlier, the standard deviation of an entire population is the square root of the variance of the entire population. This can also be calculated using the VARP function:

=SQRT(VARP(B3:B7))

Usage Notes

Here are some important usage notes to consider when using the STDEVP or STDEV.P functions in Google Sheets:

When using values as a cell/range reference:
- Blank cells, Boolean values, or text in the array/range reference are ignored. Only numbers are counted, including 0.
- Error values will cause the formula to return an error.
When using values directly in the formula:
- STDEVP/STDEV.P will return an error if any of the values are text or error values.
- Boolean values (TRUE/FALSE) evaluate to 1 or 0.
- Text representations of numbers (e.g., “100” instead of 100) will be counted.