Averaging Non-Zero Values

Written by

Editorial Team

Reviewed by

Steve Rynearson

Last updated on February 8, 2018
Download Example Workbook

Download the example workbook

It is quite easy to work out the average of a series of numbers:

181 average non zero values 327x400
We just use the average function in Excel. However what happens if we want the average of only the NON – ZERO Values.  The function COUNTIF will count up the cells in a range that meet a criteria:

COUNTIF(Range, Criteria)
The criteria must be placed in quotes –so if our criteria is greater than 0 we need the phrase “>0”. In this case our criteria is not equal to zero.  Remembering that the average of  a series of numbers is their total divided by the total number we have:

182 average non zero values 400x400

Where the “<>0” allows us to count up the non zero values in the range:

183 average non zero values 400x338

AI Formula Generator

Try for Free

Excel Practice Worksheet

practice excel worksheet

Practice Excel functions and formulas with our 100% free practice worksheets!

  • Automatically Graded Exercises
  • Learn Excel, Inside Excel!

Free Download

Return to Excel Formulas List