A Bunch Of “Average” Formulas!

Excel offers four “average” functions:
Average – gives the median value of a range of values
Averagea – gives the median value of a range of cells, treating non-numeric entires as having a value of 0
Averageif – gives the median value of a range of values based on a certain criteria
Averageifs – gives the median value of a range of values based on multiple criteria

Let’s look at an example of each.  Here is a table of Sales data for 3 salespeople over 6 months and if they were over or under their targets: 

First, let’s look at a straight average of all sales.  In cell “I3” I’ve entered the following formula: 

Using the Average function, I’ve obtained the median value for the Sales in column “D”.  That’s pretty straight forward.   

Now, let’s see calculate the average by adding the criteria of only looking at Tom’s numbers.  The Syntax for this formula is:

The “range” is the group of cells you want to evaluate for the criteria.  Since the criteria is “Tom”, then the range of cells to be evaluated is the list of salesmen, which is B2:B19.  The criteria is “Tom”, which  you can either type in with quotes, or click on a cell that contains that label as I did, cell B2.  Finally, the average_range is the sales data you want to average, which are cells D2:D19.  The formula and results are:

Finally, we want to find the median for Tom’s sales that are Under his target.  So we have two criteria:  one is Salesman Tom, the other is Target Under.  The formula syntax is a bit different.  In Averageif, we started with the criteria range, then indicated the criteria and finally the cells to be averaged.  With Averageifs, you start with the cells to be averaged, then list the first criteria range and its criteria, then the next criteria range and its criteria and so on.  Here is the syntax for this formula:  

The average_range is the sales data in cells D2:D19.  The criteria_range1 is the Salesman list which is cells B2:B19 with the criteria1 as Tom in B2.  The criteria_range2 is the Target cells of E2:E19, with the criteria of Under in E2.  The formula and results are: 

To illustrate the difference between AVERAGE and AVERAGEA, in the example below, you will see 3 values and one text item.  Using Average, Excel will take the sum of the 3 values and divide by 3 to get an average of 4, ignoring the text.  However, using AVERAGEA, since it treats a non-valued entry as 0, the sum of the values is now divided by 4, resulting in a mean of 3:

Remember, you can always use the formula finder by clicking on the Insert Function button of the Function Library group on the Formulas tab and have Excel walk you through the formula structure:

I hope this helps explain the differences of the Average functions in Excel.   

Happy Excelling!

Free Download!

Subscribe to Download Your FREE Copy of
"My 70+ Favorite Excel Keyboard Shortcuts" Today!

No comments yet.

Leave a Reply