Microsoft Excel Statistical Formulas

Excel Chapter 21 of 24: Excel Statistical Functions and Formulas

Excel offers you 80 functions in the statistical category. Here are the ones (10) that you will use more often.

Functions	What it Does
AVERAGE	Returns the average of its arguments
AVERAGEA	Returns the average of its arguments, including numbers, text, and logical values
COUNT	Counts how many numbers are in the list of arguments
COUNTA	Counts how many values are in the list of arguments
COUNTIF	Counts the number of nonblank cells within a range that meet the given criteria
MAX	Returns the maximum value in a list of arguments
MAXA	Returns the maximum value in a list of arguments, including numbers, text, and logical values
MIN	Returns the minimum value in a list of arguments
MINA	Returns the smallest value in a list of arguments, including numbers, text, and logical values
RANK	Returns the rank of a number in a list of numbers
LARGE	Returns the k-th largest value in a data set
SMALL	Returns the k-th smallest value in a data set

MAX, MAXA, MIN and MINA

=MIN(A1:A5) will return the smallest value in the range A1 to A5. Arguments can be separated with a colon or with commas if the cells or ranges are not contiguous like in =MIN(A1,A2,A4,A5) or =MIN(A1,A2,A4:A5). MAX in an interesting functions to highlight the latest date within a range but make sure that the format of the cell where you use MIN or MAX is ''Date''.

LARGE, SMALL

And what if you want the second or third largest value or the second smallest value. Use LARGE and SMALL like this:
=LARGE(A1:A5,2), =LARGE(A1:A5,3), =SMALL(A1:A5,2)

As a matter of facts you can forget about MIN and MAX with:
=LARGE(A1:A5,1), =SMALL(A1:A5,1)

COUNT and COUNTA

If you want to count the number of cells that are not blank COUNT and COUNTA will return a different result if in one of the cells there is a text. OR A SPACE
=COUNT(A1:A5) will return 5 is only numbers OR DATES are present in cells A1 to A5 and 4 if there is a letter, an empty cell OR A SPACE in one of the cells. The SPACE thing is important to remember when you are importing data from an external source.
=COUNTA(A1:A5) will return 5 unless one of the cells is empty. If all the cells contain numbers, letters OR SPACES the result will be 5.

AVERAGE and AVERAGEA

Watch for dates! If you want the average of a range and there is a date within there is a problem because dates are numbers. If all the cells are dates, indeed you can calculate the average date of.... The difference between AVERAGE and AVERAGEA becomes evident when one of the cells contains a text OR A SPACE and don't forget the SPACE. A cell containing a space is NOT empty.

Next Chapter: SUMIF Functions in Excel

Discover more in 50 Excel spreadsheets

Excel Statistical Functions and Formulas