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


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.