If you’re looking for a way to avoid the divide by zero error during the** average calculation in Excel**, you’ve come to the right place. There are some methods for avoiding the divide by zero error in Excel’s average calculation. This article will walk you through each step with illustrations so you can easily apply them to your situation. Let us now move on to the meat of the article.

## Download Practice Workbook

You can download the practice workbook here:

## When Does #DIV/0! Error Occur in Average Calculation?

While doing the average calculation of various types of datasets, you may find **#DIV/0! Error** in the cell. This may happen for certain reasons.

**If you want to calculate the average of text values**

Sometimes, you may insert an **average **formula for the range of cells that may contain text values. If any text value comes into the average formula, it will return **#DIV/0! Error **as an output. As a result, when selecting the range for the AVERAGE function, make sure that the cells do not contain text values.

**If the numbers are in TEXT format**

You can find any Excel file where the numbers have been converted to **TEXT **format. In this case, you can’t use the **AVERAGE **function to** calculate the average of the numbers,** and if you do, you’ll get **#DIV/0! Error.**

## 2 Cases of Divide by Zero Error (#DIV/0!) While Calculating Average in Excel and Their Solutions

In this section, I will demonstrate how to avoid the divide-by-zero error when calculating the average in Excel. In this article, you will find thorough explanations with illustrative examples for every topic. Here, I have utilized the **Microsoft 365 version**. However, you may use any other version depending on your availability. Please leave a comment if any part of this article does not work in your version.

### Case 1: Selection of a Wrong Range in AVERAGE Function

It may happen sometimes when you have selected the wrong range of cells to calculate an average.

- Suppose, you want to calculate the average sales of all products as shown in the screenshot below. Here, I have an Excel worksheet containing sales data for some tech products.

- Here, to calculate the average sales, I should use the data range
**D5:D14**in the**AVERAGE function**but I have to use the reference of cell range**C5:C14**. So, Excel has tried to calculate the average of device names which is impossible. So, it returns**#DIV/0! Error**as an output.

- Now, I have
**corrected**the cell reference which contains only**numerical**data, and it is giving the average value of sales properly.

**Read More: How to Calculate Average True Range in Excel (with Easy Steps)**

**Similar Readings**

**How to Calculate Average of Multiple Columns in Excel (6 Methods)****How to Add Average Line to Excel Chart (with Easy Steps)****Average Values Greater Than Zero in Excel (4 Ways)****How to Ignore #N/A Error When Getting Average in Excel****How to Calculate Average Share Price in Excel (2 Easy Ways)**

### Case 2: If Numbers Are in Text Format with Leading Apostrophes

Let’s say a coworker gave you an Excel file with some sales information in it. and you have assumed the sales values are in number format as usual. But, you are facing **#DIV/0! error **as an output while calculating the average of the sales values. And, you have **double**–**checked** that there isn’t any text value in the selected cell.

- This is happening because the previous user or the creator of the
**Excel**file has converted the numbers to Text format by adding an**apostrophe (`)**in front of the numbers. So, you have to remove the**apostrophe**manually from each cell of the range to calculate the**average**. There is another way to remove the apostrophe from the front of numbers which is using the**Text to Column**feature of Excel.

**Solution: Remove Leading Apostrophe from Numbers with Text To Column Feature**

- For this, first, select the cells which contain
**apostrophes**in front. - Then, go to the Data tab in the top ribbon and select the
**Text to Column**feature in the**Data Tools**

- Then, a new window will appear. Here, select the “
**Delimited**” options and press**Next**after that.

- In the
**2nd step,**you have to specify the**delimiter**which is separating the text. - Here, mark the
**Other checkbox**and insert an**Apostrophe**in the box. - Then, press the
**Next**button.

- In the third and the last step, you can select
**General**as the column data format. - Then, specify the first cell of the selected data range as the
**destination**. - Finally, press the
**Finish**

- As a result, you will see that, the
**#DIV/0! Error**vanishes and the average of the selected data range is shown in the box. Thus, you have removed the**#DIV/0! Error**and calculated the**average**of a data range in Excel.

**Read More:** **How to Average Negative and Positive Numbers in Excel**

## Conclusion

In this article, you have found how to avoid the divide by zero error during the average calculation in Excel. I hope you found this article helpful. You can visit our website, **ExcelDemy**, to learn more Excel-related content. Please leave comments, suggestions, or queries if you have any in the comment section below.

## Related Articles

**How to Calculate Daily Average from Hourly Data in Excel****Calculate Average Growth Rate in Excel (3 Easy Methods)****How to Calculate Average of Averages in Excel (with Easy Steps)****Calculate Average Only for Cells with Values in Excel****How to Calculate Average of Text in Excel (2 Ways)****Average Attendance Formula in Excel (5 Ways)**