jump to navigation

How to Find Average Without Including 0 (zero) or Error Data in Excel March 4, 2010

Posted by Admin in Tutorials, Tips & Tricks.
Tags: , , , , , ,
add a comment

Few days ago, a friend of mine asked me how to find an average of a set of data without including 0 (zero) because it will be too small and out of purpose.

You can do it by using array formula. Usually you use =average(A1:A5) to find usual average. But this formula take 0 (zero) into calculation. To exclude 0 (zero) you can use this formula:

=average(if(A1:A5=0,””,A1:A5))

After finishing typing the formula, you can’t just press enter. But you need to press Ctrl+Shift+Enter it the same time because this is array formula. You can also use other function like sum, min, max, stdev, etc.

With the same logic you can use it to handle error data. When there is even one error data (#N/A, #DIV/0!, #REF,etc) in your data set. Any function you use like average, sum, max, min, etc, will result an error (#N/A, #DIV/0!, #REF, etc) as well. To avoid this you can use above tricks with a little change. Use this to find average and handling error:

=average(if(iserror(A1:A5),””,A1:A5)) don’t forget to press Ctrl+Shift+Enter

With that formula, error in the data will be ignored in the calculation.

Hopefully useful.