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