Statistical Funtions

In this tutorial I will show you how to use some of Excel’s very useful statistical functions. If you’re doing any data analysis work for your job, I’m sure you’re going to use these a lot.

AVERAGE function

Use the AVERAGE function to calculate the average of a range of cells. To do this simply insert the function and select the range of cells of which you want to find the average.

Using the AVERAGE function in Excel

AVERAGEIF function

Use the AVERAGEIF function to calculate the average of a range of cells based on one criteria. To do this simply insert the function and select the range of cells containing data.

Using the AVERAGEIF function in Excel

In the example I use the logical operator <> which means ‘not equal to’.
Additionally next to AVERAGE and AVERAGEIF you can also use the AVERAGEIFS functions to select an average based on multiple criteria – simply add more arguments with criteria to the function.

MEDIAN function

If you want to find the median of a range of cells (the middle number value) then you should use the the MEDIAN function. To do so simply insert the function and select the range of cells containing data.

Using the MEDIAN function in Excel

The number 3 means that half of the numbers in the selected range are higher than 3, and half of them lower than 3.

MODE function

In case you find the most frequently occurring number in a range of cells, you should us the MODE function. To do this simply insert the function and select the range of cells containing data.

Using the MODE function in Excel

The 0 number shows that the number 0 is occurring most often in the selected range.

Standard Deviation function

A well-known concept in statistics is that on of standard deviation. If you want to get it from a range of cells, use the STEDV.P function. To do this simply insert the function and select the range of cells containing data.

Finding the standard deviation of a range of cells in Excel

Note that Excel offers various standard deviation functions.

• STDEV.P: Calculates standard deviation based on the entire population given as arguments.
• STDEV.S: Estimates standard deviation based on a sample.
• STDEVA: Estimates standard deviation based on a sample, including logical values and text.
• STDEVPA: Calculates standard deviation based on the entire population, including logical values and text.

For more information on what standard deviation means exactly, check out more on this topic on wikipedia.

MIN function

Use the MIN function to find the minimum value in a range of cells. To do this simply insert the function and select the range of cells containing data.

Using the MIN function in Excel

MAX function

Use the MAX function to find the maximum value in a range of cells. To do this simply insert the function and select the range of cells containing data.

Using the MAX function in Excel

LARGE function

Use the LARGE function to find the 2nd or 3rd largest number in a range of cells for example. To do this simply insert the function, select the range of cells, and which number value you want to find (2nd largest, 3rd largest, etc).

Using the LARGE function in Excel

SMALL function

Use the LARGE function to find the 2nd or 3rd largest number in a range of cells for example. To do this simply insert the function, select the range of cells, and which number value you want to find (2nd largest, 3rd largest, etc).

Using the SMALL function in Excel