How can I calculate the average in Google Sheets while ignoring zero and blank cells?

By /

To calculate the average in Google Sheets while ignoring zero and blank cells, you can use the AVERAGEIF function. This function allows you to specify a range of cells to average, while also setting a criteria for which cells to include in the calculation. By setting the criteria to “not equal to zero” or “not blank”, the function will only average the values from cells that meet that criteria. This allows you to exclude any unwanted zero or blank cells from the average calculation.

You can use the following formula to calculate the average in Google Sheets while ignoring zeros and blank cells:

=AVERAGEIF(B2:B11, "<>0")

This particular formula calculates the average value in the range B2:B14 and ignores cells that are equal to zero or blank.

Note: Google Sheets ignores blank cells by default when calculating an average.

Thus, we only need to specify in the formula that cells should not be equal ( “<>0”) to zero as well.

The following example shows how to use this formula in practice.

Example: Calculate Average & Ignore Zero and Blank Cells in Google Sheets

Suppose we have the following dataset in Google Sheets that shows the total sales made by various employees at a company:

If we simply used the AVERAGE function then we would calculate the average sales for all of the employees who had a non-blank value:

The average sales per employee who had a non-blank value is 6.125.

However, suppose we wanted to only calculate the average for employees who had a sales value that was not blank and not equal to zero.

We could type the following formula into cell D2:

=AVERAGEIF(B2:B14, "<>0")

The following screenshot shows how to use this formula in practice:

Google Sheets average ignore zero and blanks

This formula calculated the average by only using the values that were not blank and not equal to zero.

We can confirm that this is correct by manually calculating the average of all values that are not blank or equal to zero:

Average of Values Greater than Zero: (10+4+14+6+3+12) / 6 = 8.167.

This matches the value calculated by our formula.

Additional Resources

The following tutorials explain how to perform other common operations in Google Sheets:

x

Related terms:

  1. How can Power BI calculate an average while ignoring zero values?
  2. How do you replace blank cells with zero in Google Sheets?
  3. How to Calculate Average If Not Blank in Google Sheets
  4. How do I replace blank cells with zero in Excel?
  5. How do I create a chart in Google Sheets that ignores blank cells?
  6. How do I make Google Sheets ignore blank cells with formulas
  7. How to sum cells if they are not blank in Google Sheets?
  8. How can I use IFERROR in Google Sheets to make cells blank?
  9. Excel: How to average only cells that are not blank?
  10. How to calculate the average of a range only if greater than zero in Excel?