Google Sheets: How to Calculate Average If Not 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:

Since every single value was missing in column B, the formula simply returned a blank.

Additional Resources

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

Google Sheets: How to Sum Cells If Not Blank
Google Sheets: How to Replace Blank Cells with Zero

Leave a Reply

Your email address will not be published.