# Statistical functions - worksheet

## Statistical

Statistical functions are grouped under the Statistical category in the Excel function library, and the Insert Function dialog box. Argument descriptions vary between array, number, and value. Here is a list of the more commonly used functions.

Excel statistical functions - a selection | Description |
---|---|

1. AVERAGE(number1, [number2], …) |
Returns the average (arithmetic mean) of the arguments. • The measure of central tendency. Moment ordinal 1 |

2. CORREL(array1, array2) |
Returns the correlation coefficient of two arrays (cell ranges) |

3. COVARIANCE.P(array1, array2) |
Returns population covariance of two arrays (cell ranges). • The measure of dispersion. Moment ordinal 2 |

4. COVARIANCE.S(array1, array2) |
Returns sample covariance of two arrays (cell ranges) |

5. COUNT(value1, [value2], …) |
Returns a count of the number of numeric values |

6. COUNTA(value1, [value2], …) |
Returns a count of the number of cells that are not empty |

7. KURT(number1, [number2], …)T |
Returns the kurtosis characterizes the relative peakedness (positive kurtosis) or flatness (negative kurtosis) of a distribution compared with the normal distribution. • A measure of shape. Moment ordinal 4 |

8. MAX(number1, [number2], …) |
Returns the largest value in a set of values. |

9. MEDIAN(number1, [number2], …) |
Returns the median of the given numbers - the number in the middle of a set of numbers |

10.MIN(number1, [number2], …) |
Returns the smallest value in a set of values. |

11. STDEV.P(number1, [number2], …) |
Returns the standard deviation based on the entire population of values |

12. STDEV.S(number1, [number2], …) |
Returns the standard deviation based on the entire population of values |

13. SKEW.P(number1, [number2], …) |
Population - Returns the skewness of a distribution - the degree of asymmetry of a distribution around its mean. Positive skewness means the tail of the distribution extends toward more positive values. Negative skewness means the tail of the distribution extends toward more negative values. • A measure of shape. Moment ordinal 3 |

14. SKEW(number1, [number2], …) |
Sample - Returns the skewness of a distribution, based on the sample Moment ordinal 3 |

## Math and Trigonometric

The **SUM** and **SUBTOTAL** functions are listed in the Math and Trigonometric category.

Excel math and trigonometric functions - a selection | Description |
---|---|

14. SUM (number1, [number2], …) |
Returns the sum of the arguments. • Add the values. |

15.SUBTOTAL(function_num,ref1, [ref2], …)) |
Returns a subtotal in a list or database. See subtotal function for details including a list of function_num values |

## Algebraic formulas

- 1. Mean (average)
- Population: \( \mu = \frac{\sum {x}}{N} \)
- Sample: \( \bar x = \frac{\sum {x}}{n} \)

- Variance
- 3. Population: \( \sigma ^ 2 = \frac{\sum {(x-\mu)^2}}{N} \)
- 4. Sample: \( s ^ 2 = \frac{\sum {(x- \bar x)^2}}{n - 1} \)

- 7. Kurtosis
- Population: \( \mu _ 4 = \frac{\sum {(x-\mu)^4}}{N} \)
- Sample: \( k _ 4 = \frac{n^2}{(n - 1)(n - 2)(n - 3)} \times \left[ (n+1) \frac{\sum {(x- \bar x)^4}}{n} - \frac{3(n-1)^3 s^4}{n^2} \right] \)
- Excel help file: \( k _ {ws} = \left\{ \frac{n(n+1)}{(n-1)(n-2)(n-3)} \sum{\left( \frac{x- \bar x}{s} \right)^4} \right\}- \frac{3(n-1)^2}{(n-2)(n-3)} \), see support.microsoft.com for details

- Skewness
- 13. Population: \( \mu _ 3 = \frac{\sum {(x-\mu)^3}}{N} \)
- 14. Sample: \( k _ 3 = \frac{n}{(n - 1)(n - 2)} \sum {(x- \bar x)^3} \)

- This example was developed in Excel 365 ProPlus 64 bit.
**Published:**25 March 2020**Revised:**Thursday 26th of March 2020 - 01:07 PM, [Australian Eastern Standard Time (EST)]