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**, **Selle**r, 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.

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

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

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

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

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

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

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

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

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

**๐ 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**.

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

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

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