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 c**ontext 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**–

`=AVERAGE(D5:D12*1)`

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**–

`=AVERAGE(IF(ISERROR(D5:D12)," ",D5:D12))`

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**–

`=AGGREGATE(1,6,D5:D12)`

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)**