There are many areas within business reporting where we are looking for the average of a selection of results.
A good example of this may be when we are looking to find the average value of sales made within a particular month, within a particular region, or to an individual customer.
To find the average all we need to do is add a selection of numbers together and then divide the total by the number of values that make up the total, i.e., the sum of the sales made divided by the number of sales.
We can easily find the average in Excel by manually creating an average formula, or by using the SUM, COUNT, AVERAGE, AVERAGEIF or AVERAGEIFS functions, the SUBTOTAL function or a Pivot Table.
Note: the AVERAGE, AVERAGEIF and AVERAGEIFS functions are arithmetic mean calculations and can be found under the Statistical functions group on the Formulas tab.
Manually creating an Average formula in Excel
Let’s look at a scenario. Let’s say we have a set of sales records for a particular week, and we want to analyse the data. In the example below we are wanting to analyse the total sales dollars made, the number of sales made and the average sales value.
To insert the total sales dollars made in cell B3 we will use the SUM function.
To insert the number of sales made in cell B4 we will use the COUNT function.
Note: the COUNT function will only count cells that contain a value. To count cells that contain any type of data use the COUNTA function instead.
Creating an Average formula – step by step
Now that we have the total sales dollars and total number of sales made we can easily write an average formula simply by dividing the total sales dollars in cell B3 by the number of sales in cell B4.
Note: calculating an average this way can create problems if your data contains zeros or errors. Please refer to the ‘AVERAGE cells containing errors or zeros’ notes below for tips on how to avoid this.
Tip: If we didn’t require the Total $ and No. of sales results to be displayed on the worksheet we could just click into cell B3 and create the average using the formula =SUM(J11:J22)/COUNT(J11:J22).
Calculate average using the AVERAGE function
An even quicker and easier way to create an average formula in Excel is by using the AVERAGE function.
Thankfully, we can use the AVERAGE function without the need to find the SUM or the COUNT for a set of data.
AVERAGE function – step by step
Let’s revisit our scenario. For us to quickly find the average sales figure for our data set all we need to do is click into cell B3 and wrap the AVERAGE function around the cells that contain our sales values.
The syntax is =AVERAGE(number1, [number2], ...)
In our example we will use the range of cells in the Total Sales $ column as the number arguments. The average formula will be =AVERGE(J6:J17).
Using the AVERAGE function, Excel calculates the total value of the range and divides it by the number of cells containing data, to return the average value.
AVERAGEIF function in Excel
To analyse the data even further we can use the AVERAGEIF function to determine an average based on set criteria.
One of the fantastic things about the AVERAGEIF (and AVERAGEIFS) function is that the criteria can be in form of a number, expression, cell reference, or text that define which cells will be averaged. For example, criteria can be expressed as 500, "500", ">500", "Peter", or C10.
Returning to our sales data, what if we wanted to know the average dollar value of sales made by a member of our sales team. In this example we will look at sales made by Peter.
AVERAGEIF function example – step-by-step
To do this we can utilise the AVERAGEIF function. The syntax for the AVERAGEIF function is
In our example the range to evaluate is K6:K17. The criteria we are looking for in this range is “Peter” and the range we will average is J6:J17.
Excel will look down the range, find any rows where Peter’s name is present, and then on the same row move over to the average_range and add this value to the total sales value for Peter’s sales. It will then divide this figure by the total number of sales made by Peter.
AVERAGEIFS function in Excel
The AVERAGEIFS function allows us to do an even deeper dive into the data to determine an average based on multiple conditions (criteria).
For example, what if we wanted to know the average dollar value of sales made by Peter on a certain day.
AVERAGEIFS will allow you to have up to 127 criteria!
AVERAGEIFS function example – step-by-step
The syntax for the AVERAGEIFS function is
In our example the average_range is K6:K17, the Total Sales $ column. The argument criteria_range1 is L6:L17 which contains names of the salesperson who made each sale.
The criteria1 is asking Excel to find the word “Peter” within the criteria_range1 range.
Once the first criteria has been met we then ask Excel to carry on and find only records that meet criteria2 which is to find sales made on the 10th of November.
In setting multiple criteria using an AVERAGEIFS function we can easily calculate sales made by Peter on the 10th of November.
Average cells that contain errors or zeros
The AVERAGE, AVERAGEIF, and AVERAGEIFS functions all automatically ignore blank cells and any cells that contain text. However, cells that contain zeros or errors can cause problems.
In the example below cells B3, B5 and B6 are all showing #VALUE errors. This is because the formulas in each of these cells are referring to the range J11:J22 which contains an error in cell J14.
Additionally, cell B4 is not including row 14 in the count result but is including J15 and J17 as these cells hold a zero.
Unfortunately counting zeros or including errors can distort your results, leaving you with an incorrect result. For example, when using the formula =SUM(J11:J22)/COUNT(J11:J22) the COUNT function will include zeros but exclude cells that hold an error. Using a COUNTA instead would count ALL cells containing data including zeros and errors, however the formula still isn’t helpful if the SUM range has errors in it.
To overcome errors and exclude cells that contain zeros, you could use the AVERAGE function and include in the arguments only the cells you want to average. This is how the result has been calculated in cell B7.
However, this option isn’t great when you have a sizeable range of data.
Instead try using the AVERAGEIF function and set the criteria to “>0”. This function has been used in cell B8 so that Excel will only average the cells that contain data and ignore the errors and any cells containing zero.
Average filtered data using SUBTOTAL
To create an average formula on filtered data in Excel try using the SUBTOTAL function.
Let’s once again look at an example using our sales data.
First we will select the table of sales data and then from the Data tab select Filter to turn on the filtering options.
Next, in cell B3 we will enter the SUBTOTAL function. The SUBTOTAL function creates a total based on a range, however it excludes filtered-out cells.
The syntax is SUBTOTAL(function_num,ref1,[ref2],...). The function_num specifies the function to use to create the subtotal.
In our example the function_num is 1. This tells Excel to create an average. The ref is the range K6:K17. This range includes the entire list of sales values before a filter has been applied.
Tip: you can substitute the 1 for a 2 to perform a COUNT, 3 for a COUNTA and 9 for a SUM.
With the SUBTOTAL function now created using the entire list as the range reference, we can now filter the data and watch the total change.
For this example, we will filter on the Date column so that only sales made on the 10th of November are displayed.
We can now easily see the total sales for this day. In the example below you can see that the total has been updated in cell B3.
Tip: you can also cross-check your average formula by selecting the visible cells and checking the Average total in the Status bar at the bottom of the screen.
Average filtered data example using Excel Tables
Another super quick way to create an average formula on filtered data in Excel is to use Tables.
Let’s once again look at an example using our sales data.
First we need to make sure each of the columns of data has a heading. We will then select the sales data and then from the Insert tab select Table to apply a Table over the selected cells.
The Create Table dialog box will be displayed. We will leave the My table has headers option checked and then click OK.
A Table will now be inserted over the selected data.
From the Table Design tab, we will select the Total Row option.
Tip: to see the Table Design tab you need to be clicked on a cell inside the Table area.
Now we will select cell K16, which is a cell within the Total row area. This is where we will be displaying the average formula for the Total Sales $ column.
Once the cell is selected a drop-down arrow will be displayed. To create an average total just select the Average option from the drop-down list.
Tip: creating an average formula this way automatically inserts the SUBTOTAL function for you. However, this function uses a function_num argument of 101 not a 1. Use 101 if you would like any hidden rows excluded along with filtered-out rows. Change the 101 to 1 if you would like hidden rows included.
How to calculate Average in a Pivot Table
If we continue with our sales data example, we can quickly calculate averages using a Pivot Table.
Let’s say we wanted to see the average $ value of sales made on each individual day.
To do this, we would first select the data and then from the Insert tab select Pivot Table.
The PivotTable from table or range dialog box will appear. Click OK.
We can now click and drag the Date field into the Rows area and the Total Sales $ field into the Values area.
By default, Excel will calculate the total sales made using the SUM function. In our example, this shows us the total sales $ made each day.
We can now change the totals to display an average. To do this we can right click any value in the column and then from the shortcut menu select Summarize Values By, then Average.
The values will then be updated to display the average. In our example the column is now showing the average sales $ made each day.
Tip: to show a SUM and an AVERAGE column just put the same field into the Values area twice, and then change one of the columns to an Average.
5 reasons your Excel Average formula may not working
It can be very frustrating when your average formula isn’t producing the results you expected.
There are several reasons this may be happening. I’ve listed the most common reasons below.
Problem #1: Your average is including cells that hold a 0 which is causing your average to be incorrect.
Fix #1: refer to the steps in the 'Average cells that contain errors or zeros', instruction above.
Problem #2: Your average formula is showing a #DIV/0 error.
Fix #2: the range your average formula is calculating is empty. Either reset the range so that it is referring to a list of numbers or you can hide the error by wrapping an IFERROR function around the average formula.
The IFERROR function will check to see if there is an error and if there is return an empty cell (“”).
Problem #3: your data is stored as text, not numbers, therefore they will not be included in the average formula result.
Indicators that a number is stored as text are when in the Formula Bar you can see an apostrophe in front of the number.
Or, when Excel shows a small green triangle in the top left corner of the cell and a warning icon to the side of the cell. Mousing over the warning icon shows the warning The number in this cell is formatted as text or preceded by an apostrophe.
Note: sometimes there isn’t an indicator shown and even though it looks like a number, it actually is stored as text. Try the steps below so that you can eliminate this as being the problem.
Fix #3 select the list of cells you are trying to average and then on the Data tab click Text to Columns.
The Convert Text to Columns Wizard – Step 1 of 3 dialog box will open. Make sure Delimited is selected and then click Next.
On Step 2 of the Columns Wizard remove all checks from the Delimiter options and then click Next.
The numbers will now be changed to values and your average formula should now be working.
Problem #4 the average formula is producing an incorrect result.
Fix #4 check the following:
- The average formula doesn’t contain constants, e.g., =SUM(K6:K17)/12. If another row is entered into the list the average formula may update to =SUM(K6:K18)/12 which is now incorrect as there is now an additional row. The 12 would need to be changed to 13 for it to average correctly. Therefore, the best average formulas to use would be either =SUM(K6:K18)/COUNT(K6:K18) or =AVERAGE(K6:K18).
- The data may contain zeros or errors. Refer to the steps in the ‘Average cells that contain errors or zeros , instruction above.
Problem #5 the average formula isn’t updating when more data is added.
Fix #5 First check the range or ranges in your Average formula are including the rows or columns with the new data. the file may have been set to manual calculation. To turn the calculations back to Automatic, from the Formulas tab click Calculation Options and then click Automatic.
Was this blog helpful? Let us know in the Comments below.
If you enjoyed this post check out the related posts below.