# 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 (alphabetical order) | 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). |

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 • The measure of dispersion. Moment ordinal 2 actual units |

12. STDEV.S(number1, [number2], …) |
Returns the standard deviation based on the entire population of values • The measure of dispersion. Moment ordinal 2 actual units |

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 |

15. VAR.P(number1, [number2], …) |
Sample - Returns the variance, based on the entire population (ignores logical values and text in the population) • The measure of dispersion. Moment ordinal 2 units squared |

16. VAR.S(number1, [number2], …) |
Sample - Returns the variance, based on the sample (ignores logical values and text in the sample) • The measure of dispersion. Moment ordinal 2 units squared |

## Math and Trigonometric

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

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

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

18.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) - a measure of central tendency
- Example: Calculate the average mark of assignment groups in a class. Add the individual assignment marks \(a\) and divide by the number of assignments \(n\)
- Population: \( \mu = \frac{1}{n} \sum_{i=1}^{n} { a_i} \)
- Example: Estimate the average return for a company stock based on a sample size \(n\) of return data \(r\)
- Sample: \( \hat{\mu} =\frac{1}{n} \sum_{i=1}^{n} {r_i} = \frac{1}{n}(r_1+r_2+ \ldots +r_{n-1}+r_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 _ {4ws} = \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 This is the sample excess kurtosis

Sample kurtosis, not an inbuilt function, is: \( k _ {4ws.} = \frac{n(n+1)}{(n-1)(n-2)(n-3)} \sum{\left( \frac{x- \bar x}{s} \right)^4} \)

- 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} \)
- Excel help file: \( k _ {3.ws} = \frac{n}{(n-1)(n-2)} \sum{\left( \frac{x- \bar x}{s} \right)^3 } \), see support.microsoft.com for details.

### References

Kenney J F, (1943), *Mathematics of Statistics, part one*, Chapman and Hall, London.

Kenney J F, (1943), *Mathematics of Statistics, part two*, Chapman and Hall, London.

Miller M B, (2014), *Mathematics and Statistics for Financial Risk Management*, 2nd ed, Wiley.

- This example was developed in Excel for Office 365 ProPlus 64 bit.
**Published:**25 March 2020**Revised:**Wednesday 23rd of August 2023 - 09:21 AM, [Australian Eastern Standard Time (EST)]