Table of Contents
In Excel, to find the average of only cells that are not blank, you can use the AVERAGEIF function. This is a statistical function that allows you to specify a range of cells and a criteria to ignore blank cells and only average the cells that contain values. To use this function, enter the formula =AVERAGEIF(range,”<>”) into the cell where you want the average value to appear. The range is the range of cells that you want to average and the criteria is the criteria that you want to ignore blank cells. The formula should then calculate the average of only the cells that are not blank.
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.