In **Excel**, there is an excellent technique that you can use to compute the average excluding 0 (zero). There will be a significant difference between the computing average excluding 0 and the computing average including 0 in **Excel**. We have used two **Excel **functions (**AVERAGE** and **IF**) to compute the average excluding 0. In this article, we will show you **how to compute the average** in **Excel **excluding 0 with 2 easy approaches. We have also included some relevant concerns at the end of this article. Letโs say we have a dataset with grades and points for different subjects of a certain semester. We will calculate the **Grade Point Average **(**GPA**) excluding the **F **grade. So, letโs get started.

## ๐ย Download Excel File

Download free Excel workbook from here:

## Learn to Compute Average Excluding 0 in Excel with These 2 Approaches

While writing this article, โHow to Compute Average Excluding 0 in **Excel**โ, weโve considered a dataset that contains approximately **5** columns and **15** rows. In this dataset, we added **Subject**, **Grade**, **Point**, and **GPA** to make you better understand. But if you want, you can change the entities and put your own values.

**APPROACH**

### 1. Using AVERAGEIF Function

If you want to compute the average excluding 0 in **Excel **then using the **AVERAGEIF **function of **Excel **will be very helpful for you. It is a simple and handy approach to achieve. You can do it easily without facing any difficulty. This function will assist you to compute the average excluding 0 in **Excel**. Follow the given steps to achieve the method.

โฌ๏ธโฌ๏ธ **STEPS** โฌ๏ธโฌ๏ธ

- Initially, select the
**Eย**cell. - Secondly, write the following formula on that cell.

**=AVERAGEIF(D6:D15, โ<>0โ)**

**๐จ Formula Breakdown **

Here,

๐ย The range is **D6 **to **D15 **where the average will be computed and **โ<>0โ **refers that the values which are greater than **0 **and will be counted.

- Thirdly, press the
**Enterย**key. - Finally, you will see the desired result you wanted to have.

Undoubtedly, this approach is one of the easiest approaches if you want to compute average excluding 0 in **Excel**.

**๐ Read More: ****3 Approaches to Calculate 5 Star Rating Average in Excel**

**APPROACH**

### 2. Adopting AVERAGE and IF Functions Combined

In the previous step, we used the **AVERAGEIF **function to compute the average excluding 0. Now we will be going to use the **AVERAGE **and **IF **functions separately. The main difference is the **AVERAGEIF **function computed the average and then excluded 0 but if you intend to use the **AVERAGE **and **IF **functions separately, there the **IF **function will exclude 0 from the range of values at first and then the **AVERAGE **function will compute the average. Maintain the following steps.

โฌ๏ธโฌ๏ธ **STEPS** โฌ๏ธโฌ๏ธ

- Primarily, you need to select the
**Eย**cell. - Secondly, write the given formula on that cell.

**=AVERAGE(IF(D6:D15<>0, D6:D15))**

**๐จ Formula Breakdown **

Here:

๐ย A cell that has a value larger than zero is defined as one in which **D6:D15<>0 **is a logical test.

๐ย **D6:D15 **is the range for computing the average.

- Thirdly, tap on the
**Enterย**key. - Finally, you will see the required result you wanted to see.

Truly, this method is a very easy way to compute the average excluding 0 in **Excel**.

**๐ Read More: ****6 Quick Ways to Calculate Average Percentage of Marks in Excel**

## How to Compute Average Including 0 in Excel

We have discussed the ways to calculate the average excluding 0 in **Excel**. In this portion, we are going to discuss computing average including 0 using the same dataset. This procedure is not very difficult to do. We will apply the **AVERAGE **function to make the process simple. You just need to follow the given instructions to achieve the approach.

โฌ๏ธโฌ๏ธ **STEPS** โฌ๏ธโฌ๏ธ

- First, you have to select the
**Eย**cell. - Secondly, write the following formula on that certain cell.

**=AVERAGE(D6:D15)**

- Afterward, you need to press the
**Enterย**key. - Eventually, you will see the required result you wanted to see.

Indeed, this method is very simple and handy to work on.

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

## How to Calculate Average Ignoring Blanks in Excel

Sometimes we want to calculate the average by ignoring the blank cells in **Excel**. To do that, we will first make the cells blank that are containing no value or 0. After that, we will apply the **Excel **function to compute the average. The resultant will be the value without blank cells or ignoring the blank cells. Maintain the following steps so that you can do it easily.

โฌ๏ธโฌ๏ธ **STEPS** โฌ๏ธโฌ๏ธ

- Initially, you need to go to the
**Fileย**tab. - Secondly, choose the
**Options**.

- Then you will get a dialogue box name
**Excel Options**. - Thirdly, select the
**Advancedย**option. - Afterward, uncheck
**Show a zero in cells that have zero value**. - Click on the
**OKย**button.

- Now you will see that the cells containing 0 are blank.

- Belatedly, you have to select the
**Eย**cell. - After that, insert the following formula on that certain cell.

**=AVERAGE(D6:D15)**

- Then press the
**Enterย**key. - Consequently, you will see the required result you wanted to see.

Undoubtedly, you can easily compute the average by ignoring the blank cells in **Excel **by following the above approach.

**๐ Read More: ****6 Ways to Solve When Average Formula is Not Working in Excel**

## ๐ Important Notes

You should be aware of the following things while performing the processes mentioned above:

๐๏ธย You should be aware of using **Excel **functions while applying formulas.

๐๏ธย You need to practice if you have less knowledge about using the functions of **Excel**.

## ๐ Takeaways from This Article

If we summarize the whole article, we have got some points.

๐ย Initially, we applied the **AVERAGEIF **function to compute the average excluding 0 in **Excel**.

๐ย Secondly, we used the **AVERAGE **and **IF **functions separately.

๐ย Afterward, we tried to show you how to compute the average including 0 in **Excel**.

๐ย Finally, we computed the average ignoring the blank cells in **Excel**.

## Conclusion

We wish that you would be able to compute the average excluding 0 in **Excel** using this article. As several methods have been shown here, you can easily follow which one is best suited for you. If you have any confusion, feel free to ask me in the comment. In the end, to learn more about **Excel** tactics, we recommend you to visit our website **www.ExcelDen.com**.

## Related Articles

**7 Ways to Calculate Average Attendance with Formula in Excel****3 Approaches to Calculate Average of Text in Excel****How to Calculate Average Percentage Change in Excel****5 Ways to Calculate 7-Day Moving Average in Excel****Calculate Displaced Moving Average with Formula in Excel****2 Examples to Calculate Centered Moving Average in Excel****7 Quick Ways to Calculate Average Percentage Increase in Excel**