In this article:

Google Sheets Average If Not Zero (The Ultimate Guide in 2024)

May 8, 2024

Google Sheets AVERAGE IF not zero

The AVERAGEIF function in Google Sheets doesn't have a specific "not zero" version, but it can be used to calculate the average of numbers that are not zero by setting an appropriate criterion. This is a useful technique for analyzing datasets where zero might represent missing or irrelevant data.


AVERAGEIF(range, criterion, [average_range])

range is the range of cells that you want to evaluate with the criterion.

criterion is the condition that the cells in the range must meet to be included in the average. This can be a number, expression, or text that defines which cells will be averaged.

[average_range] (optional) is the actual set of cells to average. If omitted, the cells in range are used for both evaluating the criterion and calculating the average.

Using AVERAGEIF Not Zero in Google Sheets

Follow the steps below to use the AVERAGE IF not 0 function in Google Sheets to calculate the average of a range of numbers while excluding zero values. 

1. Identify the Range of Numbers to Average

Look at your spreadsheet and find the range of cells containing the numbers you want to average. For example, if your numbers are in the first column from rows 2 to 11, your range is A2 to A11.

google sheets average if not zero

2. Select a Cell for the Average Result

Click on an empty cell where you would like the average result to be displayed. This is where the formula will go and where the answer will appear. Choose a cell that is not in your data range to avoid overwriting any data.

average if not 0 google sheets

3. Enter the AVERAGEIF Formula to Exclude Zero Values

In the selected cell, type the formula: =AVERAGEIF(A2:A11, "<>0"). This formula instructs Google Sheets to calculate the average of numbers in the range A2 to A11 but to ignore any cells that contain a zero. The "<>0" part of the formula is what tells Google Sheets to exclude zero values.

google sheets average if not 0

4. Apply the Formula and Calculate the Average

After typing the formula, press the Enter key. This will cause Google Sheets to execute the formula. The program will calculate the average of the numbers in your specified range and excludes any cells that contain zero.

5. Review the Average Calculation Result

Look at the cell where you entered the formula. It should now show the calculated average of your selected numbers, with zero values excluded from the calculation. If the cell shows an error or unexpected result, double-check the formula for any typos or incorrect range references.

We hope that you now have a better understanding of how to set up the Google Sheets AVERAGE IF not zero function.

Use our payment due reminder software to easily set up custom reminders from your spreadsheet in just a few clicks. 

If you enjoyed this article, you might also like our article on how to set up AVERAGE IF not blank in Google Sheets or our article on how to check if a cell is empty in Google Sheets. 

If you want to learn how to attach Google Sheets to emails, we also suggest checking out our detailed guide. 

Get Google Sheets productivity and automation tips delivered straight to your inbox
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
We'll email you 1-3 times a week — and never share your information.
Get your copy of our free Google Sheets automation guide!
  • 27 pages of Google Sheets tips and tricks to save time
  • Covers pivot tables and other advanced topics
  • 100% free

Work less, automate more!

Use Lido to connect your spreadsheets to email, Slack, calendars, and more to automate data transfers and eliminate manual copying and pasting. View all use cases ->