Basic Statistics Functions in Microsoft Excel
How To Use Excels Formulas To Get Information From Spreadsheet Data
Oct 17, 2009
The list of statistical functions supplied for use by Microsoft Excel is extensive. These functions range from the very basic functions like AVERAGE, to very advanced probability functions that calculate Hypergeometric distributions. Three categories of function are shown here: functions that summarise central tendency, spread, and how individual points compare to the others.
The numbers used can be entered into an Excel worksheet cells A1 to A9:
1
3
3
3
4
7
8
9
9
Excel Functions of Central Tendency
These are numbers that give information about the general region that the data points lie in. The most commonly used of these is the average, sometimes called the mean. The functions that Excel provides are:
=AVERAGE(A1:A9)
this will give the mean, 5.22…
=MEDIAN(A1:A9)
This gives the number “nearest the middle” i.e. 4, because there are nine numbers, and the fifth highest is 4.
=MODE(A1:A9)
The mode is the number that appears most, i.e. 3.
Excel Functions to Measure Spread
These functions give the user an idea of how spread out the numbers are. If every number was the same, the spread is zero no matter how it is measured. In Excel, there are four main functions to use:
=STDEV(A1:A9)
This is the standard deviation, a measure of how far, on average, each point is from the mean. The answer in this example is 3.03
Similar to STDEV is the function that calculates the Population Standard Deviation:
=STDEVP(A1:A9)
The result is 2.86, which is close to 3.03, the standard deviation. The Population Standard Deviation is used when estimating the standard deviation of a population from a sample of the population.
Both standard deviation and population standard deviation are explained fully in the articles What Is Standard Deviation and How Standard Deviation is Used.
The other two Excel functions are
=VAR(A1:A9) which gives 9.19 and
=VARP(A1:A9) which results in 8.17
These represent the Variance of the numbers. The variance is the standard deviation squared.
Excel Functions Giving Information About Points in The Sample
There are at least eight functions that may be used to give useful information about individual points. Some of thes are:
=COUNT(A1:A9)
This will give a result of 9. If any of the nine numbers is replaced with text, the result would change to 8. i.e. Only numeric values are counted.
=MAX(A1:A9) returns the largest value in the list, i.e. 9, and
=MIN(A1:A9) returns the smallest value of 1.
Related to these two functions are the functions LARGE and SMALL. To find the third largest value, the function
=LARGE(A1:A9,3) will return the number 8, and similarly
=SMALL(A1:A9,3) gives the third smallest value, 3.
To find out how large a cell is compared with others, the RANK function is used:
=RANK(A1,A1:A9,1) will give the rank of cell A1 compared to the numbers in cells A1 to A9, measuring from low to high, i.e. 1. The third element of the function, 1, defines whether to give the order in ascending or descending order. So
=RANK(A1, A1:A9,0) or
=RANK(A1,A1:A9)
both return the rank of cell A1 from high to low, i.e. 9, the ninth highest.
Excel Basic Statistics Functions Summary
Microsoft Excel provides at least 80 different statistical functions. Some of these may be used to summarise data quickly and easily, and provide useful information. Other articles on this subject include "line of best fit" functions and Fixing and Handling Excel Function Errors is also useful.
More information about Excel functions is available free on-line, or in Excel for Dummies by Greg Harvey.
 |
