Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to Average a Range of Data Ignoring Zeroes in Excel?
Generally, in Excel, we can calculate the average of a row or column directly by using the average formula. When we calculate the average in Excel, as you can see, the blank cells in the sheet are ignored automatically. But have you ever tried to find the average of cells excluding zeroes in Excel?
This tutorial will help you understand how we can find the average for a range of data by ignoring zeroes in Excel. We can complete this process by using the formulas supported by Excel in an amazingly effortless way.
Averaging a Range of Data in Excel Ignoring Zeroes
Here we will use the AVERAGEIF formula along with a range of cells to complete our task. Let us look at a simple process for averaging a range of data while ignoring zero and one. We can complete this process using the formulas.
Step 1
Consider the following image of an Excel sheet with a list of numbers as the data −
To find the average without ignoring the zeroes, click on an empty cell, enter the formula =AVERAGE(A1:A10) in the formula box, and click OK to obtain the average shown in the image below.
Step 2
But here, we need to find the average excluding the zeroes. To do so, click on an empty cell, enter the formula =AVERAGEIF(A1:A12,"<>0"), and press Enter to get the average while ignoring the zeroes, as shown in the image below. In the formula, A1:A12 is the range of numbers in our sheet.
As we can see the clear difference between both averages, we can confirm that the zeroes have been ignored successfully to get the average in the second process.
Conclusion
In this tutorial, we used a simple example to demonstrate how we can average a range of data in Excel, ignoring zeroes.
2K+ Views
