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.

The average formula in Excel


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

Dataset for using the average formula in Excel

method

1. Find and Remove Blank Spaces in Numbers

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.

Blank space in the dataset

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

=AVERAGE(D6:D11)

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

Using the Average function in Excel

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

Applying the Replace tool to find a blank space

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

Replacing the blank space with no space

  • Now we press the OK button.

Replacement in blank space

  • Finally, we get our result in cell D13.

Result for the average formula in Excel

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

method

2. Change Cell Format When Formula is Showing as Text and Not Working 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.

Using the Average function to find the average.

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

Changing the number format

  • Finally, we get our result in cell D13.

The result for the average formula

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

method

3. Delete Text Value from Cell Range

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.

Text value in the data range

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

Replacing text value with a number

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

method

4. Apply Array Formula If Any Cell is Blank

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.

Using the Average function in Excel

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

=AVERAGE(D6:D11*1)

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

Using the Array formula to find average sales

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

method

5. Check Automatic Calculation Option is On or Not

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.

Using the Average function

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

Entering a zero entry.

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

Changing the Calculation Options to Automatic

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

Result for the average formula in Excel

method

6. Fix Circular Reference If Used in Formula

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.

=AVERAGE(D6:D13)

  • We will see a warning as shown below.

Circular Reference warning in Excel

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

The result shown as 0 in the circular reference problem

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

Result for the average formula in Excel


📄 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

(Visited 19 times, 1 visits today)
Md. Ahaduzzaman

Md. Ahaduzzaman

Hello everyone i'm Md.Ahaduzzaman.I am a civil engineering graduate from BUET.I am enthusiastic and highly motivated to always learn something new to broaden my horizon and find my lacking. Hopefully my Articles will benefit you to understand and overcome your problem.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo