6 Ways to Solve When Average Formula is Not Working in Excel

In our daily life, we may require to calculate the average of some value from time to time. It may be the average obtained number by students in a class. We can also use it to calculate the Per head income of the population of a country. So the calculation of the average from some datasets is actually a very common occurrence. But sometimes we may face some issues and the Average formula may not work properly in some situations. In this article, we will discuss six different methods to solve when the Average formula in Excel is not working. We will use the Blank Space, change the TEXT format, remove any Text, utilize the Array formula, convert it into Automatic Calculation, and fix the Circular Reference if the Average formula doesn’t work. The overview snapshot of this article about what we will do is as follows.

📁 Download Excel File

Download the practice file from here.

Learn to Solve When Average Formula is Not Working in Excel with These 6 Methods

In this article, we will learn 6 ways to solve when the Average formula in Excel is not working. Let us assume that we have a dataset of a Product Sales Sheet. We have the Product, Seller, and corresponding Total Sale record. We are trying to know what are the Average Sales of the Total Sales. So the sample dataset of our product record is given below for demonstration purposes.

In this section, we will remove any blank space in the dataset to solve when the Average formula in Excel is not working. We can utilize this option if we can observe any Blank Space in our dataset. But if there is no Blank Space this method will not work. So the steps for using this method are as follows.

⬇️⬇️ STEPS ⬇️⬇️

• Firstly here we can observe a Blank Space in cell D7.

• Now we write this formula in cell D13 to get the Average Sales.

• We get a result but it doesn’t include the cell entry D7 into account.

• Now we select our data range.
• After that, we go to the Find & Select option.
• Then we go to the Replace option.

• We put a blank space in the Find what option.
• But we keep the Replace with box empty.

• Now we press the OK button.

• Finally, we get our result in cell D13.

📕 Read More: 7 Easy Ways to Calculate Average Rating in Excel

Here we will discuss how to change the Text format to solve when the Average formula in Excel is not working. This method is helpful if we mistakenly put our data as well as the formula cell in a Text format instead of a Number format. Otherwise, this procedure will not work. So the steps for using this method are as follows.

⬇️⬇️ STEPS ⬇️⬇️

• Firstly we can see that the formula in cell D13 is not working.

• So we select the data and the formula cell box.
• After that, we go to the Number Format dropdown option.
• Now we select the Currency format since our data is the currency.

• Finally, we get our result in cell D13.

📕 Read More: 3 Approaches to Calculate Average of Text in Excel

In this section, we will Delete any text value in the data range to solve when the Average formula in Excel is not working. If by any mistake any of our data entry is a Text instead of a Number then we can use this method to our advantage. Therefore the steps for using this procedure are as follows.

⬇️⬇️ STEPS ⬇️⬇️

• Here we can see a Text entry in cell D9.
• Even though the Average function yields a result but it omits the cell entry D9.

• So we simply write 0 instead of the Text value No Sale in cell D9.
• We press the Enter button & get the result in cell D13.

📕 Read More: 2 Easy Ways to Compute Average Excluding 0 in Excel

In this section, we will apply the Array Formula to solve when the Average formula in Excel is not working. If we happen to have a data entry without any value but want to include that cell while calculating the Average this method will come in handy. So the steps for using this method are as follows.

⬇️⬇️ STEPS ⬇️⬇️

• Firstly we can observe an empty cell D13 in the dataset.
• The Average function at the moment does not consider the empty cell.

• Now we go to the D13 formula box and edit the formula as this.

• Now we press the Enter button.
• Finally, we get the result in cell C13.

📕 Read More: 7 Ways to Calculate Average Attendance with Formula in Excel

In this section, we will learn how to check the Automatic Calculation when the Average formula in Excel is not working. Sometimes we make a change in our dataset but the result remains the same. This problem occurs if we have the Manual Calculation option enabled. So the steps to solve this particular problem are as follows.

⬇️⬇️ STEPS ⬇️⬇️

• Firstly here we have the Average Sales value in cell C13.

• Now we make a change in cell value C9 but the result still remains the same.

• So we go to the Formula tab.
• After that, we select the Calculation Options.
• Then we select the Automatic option.

• Finally, we get the result in cell D13 immediately.

In our final method here we will learn how to fix the Circular Reference to solve when the Average formula in Excel is not working. This problem can happen if we mistakenly give the wrong data range as the argument in the Average function. So the steps to solve this problem are as follows.

⬇️⬇️ STEPS ⬇️⬇️

• Firstly here we write this function in cell D13 and press the Enter button.

• We will see a warning as shown below.

• Then if we press the OK button we will get the result in cell 13 as 0.
• But this is not the proper result for Average Sales.

• So now we change the function argument data range from D6:D13.
• After that, we press the Enter button.
• Finally, we get the proper result in cell D13.

📄 Important Notes

🖊️  When we use the Average function we have to be careful about the function argument.

🖊️  Any change in the value will not update the calculation result if the calculation is Manual.

📝 Takeaways from This Article

📌  We can remove the Blank Space when the Average formula in Excel is not working.

📌  We need to change the data format from TEXT format when the Average formula in Excel is not working.

📌  We can apply the Array formula when the Average formula in Excel is not working.

📌  Also we need to check the Automatic Calculation option when the Average formula in Excel is not working.

📌  We can fix the Circular Reference when the Average formula in Excel is not working.

📌  We must delete any Text from the data range when the Average formula in Excel is not working.

Conclusion

In this article, we have learned six different ways to solve when the Average formula is not working. We can remove the Blank Space, change the TEXT format, Delete any Text in the data, apply the Array formula, Change it into Automatic Calculation or fix the Circular Reference if the Average formula doesn’t work. Users can use whatever they find suitable for themselves. If you want to ask any questions regarding this particular article you can always comment in our comment section. If you are interested in further Excel-related problems and solutions you can visit our website www.Excelden.com.

Related Articles

• 3 Approaches to Calculate 5 Star Rating Average in Excel
• 7 Quick Ways to Calculate Average Percentage Increase in Excel
• 6 Quick Ways to Calculate Average Percentage of Marks in Excel
• How to Calculate Average Percentage Change in Excel