[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.

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

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

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

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

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.

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

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.

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

A message box will show the result.

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

And then you will get an accurate result.

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


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.

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

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.

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

Then you will get the average accurately.

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

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.

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

Nothing to do here, press Next.

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

Again press Next.

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

Mark General and finally, just press Finish.

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

Now the AVERAGE formula is working properly.

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

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.

Remove the Text in the Dataset

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


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.

Array Formula for Blank Cells in the Dataset

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.

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

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

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

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.

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

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.

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

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

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


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.

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

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

Press OK.

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

Soon after you will get the output like this.

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

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

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

The problem is solved now.

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

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.

Find Average Ignoring Errors in Excel

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.

Find Average Ignoring Errors in Excel

⏬ 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.

Find Average Ignoring Errors in Excel


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

Mithun

Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is amazing software. Here I will post excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo