How to Average a Range in Excel and Ignore Zero (0)
Averaging a range of numbers in Excel is all about using the AVERAGE function. Simple enough.
However, if your range of numbers contains undesired zeros that you do not want to be included in the average – this is not as simple as calculating a simple average.
Fun fact, it is not even as difficult as you just thought 🧐
Extracting and importing data from external sources can often leave your data cluttered with unnecessary zeros. To learn how to ignore these zeros while you average a range out in Microsoft Excel, read this guide till the end.
Do not forget to grab your free practice workbook on your way down.
Calculating average in Excel ignoring zeros
You can calculate the average of any range in Excel making sure the zeros are ignored by using the AVERAGEIF function of Excel.
Here I have a range of numbers in Excel that has some zero values included too 🔢
To calculate the average for these numbers by ignoring the zeros:
Step 1) Write the AVERAGEIF function as follows;
Step 2) As the first argument, supply the range of numbers to be averaged out.
Step 3) As the criteria argument, write the criteria as not equivalent to zero.
The inequality sign (<>) looks like two “less than <” and “greater than >” symbols facing each other. Enclose the not equal to zero criteria in double quotation marks as “<>0”.
Kasper Langmann, co-founder of SpreadsheetoThis tells Excel to average only those cells from this range that are not equivalent to zero.
Step 4) Hit the Enter button to get the results.
Comes out as 6.2. This was easy, right? ✌
But hold on, how do we know if this is average and has accurately ignored the zeros of the range? Let’s double-check it for accuracy.
Step 5) Write the AVERAGE function
Step 6) Input the numbers of be averaged manually by leaving out the zeros of this range as follows.
I have not included Cell references A2 and A7 in the range to be averaged.
Step 7) Press enter to see the results.
Excel returns the average value as 6.2 again. Tells that you can rely on the AVERAGEIF function with any criteria of your choice 🎯
If you want to supply multiple criteria (other than only ignoring zeros) that you want followed, use AVERAGEIFS function instead.
Kasper Langmann, co-founder of SpreadsheetoConclusion
You must have used the SUMIF, COUNTIF, and other IF-based functions of Excel, but the AVERAGEIF function is rarely used.
The above tutorial aims to teach you how to effortlessly deal with the zeros in your dataset to obtain accurate averages. With this, why don’t you go through my other Excel guides that better explain the use of the IF function of Excel?
Written by Kasper Langmann
My name is Kasper Langmann, and I’m the co-founder of Spreadsheeto and a certified Microsoft Office Specialist.
This tutorial reflects over 6 hours of dedicated research and writing, based on my 10+ years of professional Microsoft Excel experience.
Last updated on April 5th, 2024.
One last thing before you go: make sure to sign up for my free Microsoft Excel course!