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