Table of Contents
Google Sheets provides a simple yet powerful way to calculate the average of certain cells, ignoring blank cells. This can be done by using the AVERAGEIF function, which takes two arguments: a range of cells and a condition. The condition can be configured to only include cells that are not blank. This allows the user to obtain the average of a range of cells, excluding any cells that have been left blank.
You can use the following formula to calculate the average value of a range in Google Sheets only if every cell in the range is not blank:
=IF(COUNT(A1:A10),AVERAGE(A1:A10),0)
This formula calculates the average value of cells in the range A1:A10. If every value in the range is blank, then the formula simply returns 0.
You could also use the following formula to return a blank instead:
=IF(COUNT(A1:A10),AVERAGE(A1:A10),"")
The following examples show how to use these formulas in practice.
Example 1: Average If Not Blank (Return a Value)
The following screenshot shows how to calculate the average value in column B where there are only a few missing values:
Since there were only a few missing values, the formula simply returned the average of the non-missing values.
Example 2: Average If Not Blank (Return a Zero)
The following screenshot shows how to calculate the average value in column B where every single value is missing:
Since every single value was missing in column B, the formula simply returned a value of 0.
Example 3: Average If Not Blank (Return a Blank)
The following screenshot shows how to calculate the average value in column B where every single value is missing:
The following tutorials explain how to perform other common operations in Google Sheets: