STDEV

 Below is an explanation of 10 different standard deviation functions in Excel, along with examples of data and formulas. Standard deviation is a measure of how spread out numbers are in a dataset. Excel provides several functions to calculate standard deviation depending on the type of data and the version of Excel you are using.

---

1. STDEV.P

• Purpose: Calculates the standard deviation for an entire population.

• Formula: =STDEV.P(range)

• Example:

  • Data: 10, 20, 30, 40, 50

  • Formula: =STDEV.P(A1:A5)

  • Result: 15.81

---

2. STDEV.S

• Purpose: Calculates the standard deviation for a sample of a population.

• Formula: =STDEV.S(range)

• Example:

  • Data: 10, 20, 30, 40, 50

  • Formula: =STDEV.S(A1:A5)

  • Result: 17.68

---

3. STDEVA

• Purpose: Calculates the standard deviation for a sample, including text and logical values (TRUE = 1, FALSE = 0).

• Formula: =STDEVA(range)

• Example:

  • Data: 10, 20, TRUE, 40, 50

  • Formula: =STDEVA(A1:A5)

  • Result: 19.36

---

4. STDEVPA

• Purpose: Calculates the standard deviation for an entire population, including text and logical values.

• Formula: =STDEVPA(range)

• Example:

  • Data: 10, 20, TRUE, 40, 50

  • Formula: =STDEVPA(A1:A5)

  • Result: 17.32

---

5. STDEV (Legacy)

• Purpose: Older version of STDEV.S for backward compatibility (calculates standard deviation for a sample).

• Formula: =STDEV(range)

• Example:

  • Data: 10, 20, 30, 40, 50

  • Formula: =STDEV(A1:A5)

  • Result: 17.68

---

6. STDEVP (Legacy)

• Purpose: Older version of STDEV.P for backward compatibility (calculates standard deviation for an entire population).

• Formula: =STDEVP(range)

• Example:

  • Data: 10, 20, 30, 40, 50

  • Formula: =STDEVP(A1:A5)

  • Result: 15.81

---

7. DSTDEV

• Purpose: Calculates the standard deviation of a sample from a database based on specified criteria.

• Formula: =DSTDEV(database, field, criteria)

• Example:

  • Database: A1:C5 (with headers: "Name", "Age", "Score")

  • Criteria: A7:B8 (e.g., "Age > 20")

  • Formula: =DSTDEV(A1:C5, "Score", A7:B8)

  • Result: Standard deviation of "Score" where "Age > 20".

---

8. DSTDEVP

• Purpose: Calculates the standard deviation of an entire population from a database based on specified criteria.

• Formula: =DSTDEVP(database, field, criteria)

• Example:

  • Database: A1:C5 (with headers: "Name", "Age", "Score")

  • Criteria: A7:B8 (e.g., "Age > 20")

  • Formula: =DSTDEVP(A1:C5, "Score", A7:B8)

  • Result: Standard deviation of "Score" where "Age > 20".

---

9. Array Formula for Weighted Standard Deviation

• Purpose: Calculates the weighted standard deviation for a dataset.

• Formula:

  excel

  Copy

  =SQRT(SUMPRODUCT((A1:A5 - AVERAGE(A1:A5))^2, B1:B5) / SUM(B1:B5))

• Example:

  • Data: 10, 20, 30, 40, 50

  • Weights: 1, 2, 3, 4, 5

  • Formula: =SQRT(SUMPRODUCT((A1:A5 - AVERAGE(A1:A5))^2, B1:B5) / SUM(B1:B5))

  • Result: 15.14

---

10. Custom Formula for Pooled Standard Deviation

• Purpose: Calculates the pooled standard deviation for two samples.

• Formula:

  excel

  Copy

  =SQRT(((n1-1)*STDEV.S(range1)^2 + (n2-1)*STDEV.S(range2)^2) / (n1+n2-2))

• Example:

  • Sample 1: 10, 20, 30 (n1 = 3)

  • Sample 2: 40, 50, 60 (n2 = 3)

  • Formula:

    excel

    Copy

    =SQRT(((3-1)*STDEV.S(A1:A3)^2 + (3-1)*STDEV.S(B1:B3)^2) / (3+3-2))

  • Result: 18.71

---

Summary Table of Functions:

Function	Purpose	Population/Sample	Handles Text/Logical
STDEV.P	Standard deviation for entire population	Population	No
STDEV.S	Standard deviation for a sample	Sample	No
STDEVA	Standard deviation for a sample	Sample	Yes
STDEVPA	Standard deviation for entire population	Population	Yes
STDEV (Legacy)	Standard deviation for a sample (old)	Sample	No
STDEVP (Legacy)	Standard deviation for entire population (old)	Population	No
DSTDEV	Standard deviation for a sample (database)	Sample	No
DSTDEVP	Standard deviation for entire population (database)	Population	No

---