Excel Downloads

Excel Online Consulting

Tips on Excel

 

Tips on VBA for Excel

Excel Index and Search Tool (Click on the buttons below)

Excel Lesson 28 of 30: Excel Statistical Functions
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
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

Tips

AVERAGE, COUNT,  MAX  and MIN might be calculated for some values in a report but you also might want to know the result of these functions for other values in the report.

You don't need a calculator when you work with Excel. Select two or more cells with numbers in them and take a look at the bottom of your screen, you will see . It is the sum of the selected cells. If you right click anywhere on the status bar, you will see this:

 

you can select this function to show the sum, the count, the average.... or nothing at all.

MAX, MAXA, MIN and MINA

What is the "A" for? With MIN and MAX I don't know.
=MIN(A1:A5) will return the same value be there a letter in one of the cells or not. If there is a date in A1:A5 the result will be a serial number unless the format of the cell in which sits the formula is "Date". Arguments can be separated with a colon or with commas if the cells or ranges are not contiguous like in =AVERAGE(A1,A2,A4,A5) or =AVERAGE(A1,A2,A4:A5). MAX and MAXA are interesting functions to highlight the latest date. MIN and MINA are interesting functions to determine the earliest date.

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.

MORE on MAX, MIN and RANK

Here is a practical example of what can be done with the MIN, MAX and RANK functions ranking sales people and finding the best and worst.

Open "excel-max-min-rank.xls"

Excel Index and Search Tool (Click on the buttons below)

Comments: Click Here

VBA for Excel macros

Excel VBA Online Consulting

Tips on Excel

 

Tips on VBA for Excel