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)]