You can use the following formulas in Excel to calculate the average value of a range if the value in a corresponding range is not blank:
Formula 1: Average If Not Blank (One Column)
=AVERAGEIF(A:A, "", B:B)
This formula calculates the average in column B only where the values in column A are not blank.
Formula 2: Average If Not Blank (Multiple Columns)
=AVERAGEIFS(C:C, A:A, "", B:B, "")
This formula calculates the average in column C only where the values in column A and B are not blank.
The following examples show how to use each formula in practice.
Example 1: Average If Not Blank (One Column)
The following screenshot shows how to calculate the average of the values in the Points column only where the values in the Team column are not blank:
The average of the values in the Points column for the rows where Team is not blank is 19.714.
We can verify this is correct by manually calculating the average of the points for the teams that are not blank:
Average of points: (22 + 17 + 28 + 30 + 12 + 11 + 18) / 7 = 19.714.
This matches the value that we calculated using the formula.
Example 2: Average If Not Blank (Multiple Columns)
The following screenshot shows how to calculate the average of the values in the Points column only where the values in the Conference and Team columns are not blank:
The average of the values in the Points column for the rows where the Conference and Team columns are not blank is 18.
We can verify this is correct by manually calculating the average of the points where the conference and team is not blank:
Average of points: (22 + 17 + 28 + 12 + 11) / 5 = 18.
This matches the value that we calculated using the formula.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
How to Calculate the Average by Group in Excel
How to Calculate Average If Cell Contains Text in Excel
How to Calculate Average If Between Two Values in Excel