[Fixed!] AVERAGE Formula Not Working in Excel (6 Solutions)

You may face some problems in some cases while using the AVERAGE formula in Excel. But no worries, there are a lot of ways to solve it. This article will provide you with the 6 best methods to overcome the situation when the AVERAGE formula is not working properly in Excel.

Download Practice Workbook

You can download the free Excel template from here and practice on your own.

6 Fixes: AVERAGE Formula Not Working in Excel

To explore the methods we’ll use the following dataset that represents some salespersons’ sales in different regions.

1. Remove Blank Space When the Average Formula Is Not Working in Excel

If the AVERAGE formula doesn’t give you the correct result then there may be blank spaces within the values. And the AVERAGE formula then skips that value and gives an answer without counting it.

Have a look that we have a blank space in the second sale.

To fix it, just remove the blank space and hit Enter.

If you have a large dataset then you can use the Find and Replace tool to find and remove the spaces.

Select the data range D5:D12.

Press Ctrl+H to open the Find and Replace tool.

Type space in the Find what box and keep the Replace with box empty.

Finally, press Replace All.

A message box will show the result.

And then you will get an accurate result.

2. Change Text to Numeric Format If the Average Formula Does Not Work

Another common reason is- maybe you have inputed the numbers in text format. So the AVERAGE formula gives you #DIV/0! Error.

A triangle-shaped green sign located in the top-left corner of the cells will give you the message that you have used the wrong format.

If you change the format from the Home tab then it won’t work.

To solve it, select the cells and click on the error sign.

Later, click Convert to Number from the context menu.

Then you will get the average accurately.

Also, you can use the Text to Columns command to do it.

Select the data range D5:D12.

Then click as follows: Data > Text to Columns.

Soon after a dialog box will open up.

Nothing to do here, press Next.

Again press Next.

Mark General and finally, just press Finish.

Now the AVERAGE formula is working properly.

Read More: How to Calculate Average of Text in Excel (2 Ways)

3. Remove the Text in the Dataset

If there are non-numeric values in your dataset, the AVERAGE formula will skip that values. Have a look at the dataset, Samuel has no sales so if we find an average then the AVERAGE formula won’t count that value. That’s why you won’t get the proper answer.

The solution is- type 0 instead of the text “No Sale” and then the AVERAGE formula will count it.

Here’s the output including zero.

Read More: How to Exclude a Cell in Excel AVERAGE Formula (4 Methods)

Similar Readings

• Calculate Moving Average for Dynamic Range in Excel (3 Examples)
• How to Calculate Average Percentage of Marks in Excel (Top 4 Methods)
• Calculate the Average of an Array with VBA (Macro, UDF, and UserForm)
• How to Calculate Average and Standard Deviation in Excel
• Calculate Average in Excel Excluding 0 (2 Methods)

4. Array Formula for Blank Cells in the Dataset

The AVERAGE function skips blank cells but to calculate the average you must have to count every cell.

To solve the problem apply the following formula in Cell D14–

Finally, press Ctrl+Shift+Enter.

Then you will get the correct answer.

Read More: Excel Formula to Find Displaced Moving Average (With Easy Steps)

5. Check for Automatic Calculation If the Average Formula Is Not Working in Excel

Excel has some options for calculating operations. If you do not activate the Automatic option and change any value, Excel will not recalculate the average.

Look at the following dataset. Here the average value is correct.

Now let’s change a value. I have changed the first sales and look that no change in the calculated average.

The reason is- I kept Manual mode in Calculation Options from the Formula tab.

To check it, click as follows: Formulas > Calculation Options.

Keep it Automatic, then it will recalculate every time for any change.

If you don’t want to turn on Automatic mode then press Calculate Now from the Calculation section or use the F9 key to recalculate for the whole workbook.

And press Calculate Sheet from the Calculation section or use the Shift+F9 key to recalculate for the active sheet.

Output after pressing Calculate Sheet, the average is changed now.

6. Check for Circular References When the Average Formula in Excel Is Not Working

If you use a Circular Reference in your formula then again you won’t get the correct result for calculating the average. Have a look at the following dataset that I have used the Cell D14 in my formula which is the cell where I am writing the formula. If I press Enter then let’s see what is gonna happen.

You will get this type of message mentioning that you have used a Circular Reference.

Press OK.

Soon after you will get the output like this.

Give proper cell reference D5:D12 to overcome this problem.

The problem is solved now.

Read More: How to Calculate Average in Excel (Including All Criteria)

Find Average Ignoring Errors in Excel

When you are working in a large dataset, using values from other sheets or cell references then it’s quite possible to remain error in values. And then the AVERAGE formula will show an error too.

But you can easily count average ignoring those errors in Excel. You can do it in 2 ways.

1. Use AVERAGE, IF, and ISERROR Functions

Combine the AVERAGE, IF, and ISERROR functions to calculate the average ignoring errors.

Type the following formula in Cell D14–

Finally, just hit the Ctrl+Shift+Enter button, because it’s an array formula.

⏬ Formula Breakdown:

➥ ISERROR(D5:D12)
The ISERROR function will check whether there is any error or not in the range D5:D12. It will return as-
{FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE}

➥ IF(ISERROR(D5:D12),” “,D5:D12)
The IF function will replace TRUE by space and FALSE by the corresponding value. So it returns-
{442589;727734;” “;999320;584738;598210;557832;784367}

➥ AVERAGE(IF(ISERROR(D5:D12),” “,D5:D12))
Finally, the AVERAGE function will calculate the average for that values. For Cell D5, it will return as-
670684

2. Apply AGGREGATE Function

Using the AGGREGATE function is the best way to do it rather than the previous way.

Type the following formula in Cell D14–

Later, just press the Enter button to get the output.

Conclusion

I hope the procedures described above will be good enough to solve the problem when the average formula is not working in Excel. Feel free to ask any question in the comment section and please give me feedback.

Related Articles

• How to Determine Triple Exponential Moving Average in Excel
• Calculate Average, Minimum And Maximum in Excel (4 Easy Ways)
• How to Calculate VLOOKUP AVERAGE in Excel (6 Quick Ways)
• Running Average: How to Calculate Using Excel’s Average(…) Function
• How to Calculate 5 Star Rating Average in Excel (3 Easy Ways)
• How to Calculate Class Average in Excel (6 Easiest Methods)