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

By /

To calculate the average of a data set in Google Sheets while ignoring zero and blank cells, follow these steps:
1. Select the range of data that you want to calculate the average for.
2. In the formula bar, type “=AVERAGEIF(range, “<>0″)”, replacing “range” with the actual range of data.
3. Press Enter to calculate the average, which will exclude any zero or blank cells in the selected range.
This formula uses the AVERAGEIF function, which calculates the average of a range based on a given criteria. By setting the criteria as “<>0”, it will only include cells that are not equal to zero in the calculation. This allows you to accurately calculate the average of a data set while ignoring any irrelevant or missing data.

Google Sheets: Calculate Average and Ignore Zero and Blanks

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 I calculate the average in Google Sheets while ignoring zero and blank cells?
  2. How can Power BI calculate an average while ignoring zero values?
  3. How do you replace blank cells with zero in Google Sheets?
  4. How can I calculate the median value in Google Sheets while ignoring zeros?
  5. How can I calculate the average in Power BI while ignoring zeros?
  6. How to Calculate Average If Not Blank in Google Sheets
  7. How do I replace blank cells with zero in Excel?
  8. How do I make Google Sheets ignore blank cells with formulas
  9. How to sum cells if they are not blank in Google Sheets?
  10. How can I use IFERROR in Google Sheets to make cells blank?