In this article we will show how to AVERAGE IF not blank in Google Sheets by using the AVERAGEIF and AVERAGEIFS functions combined with the not equal to operator <>. Simply follow the steps below:
For the purpose of the following demonstrations an example sheet was created. Access this and follow along by clicking here.
In the demonstration below we will use the Average IF function on one and two data sets, not including blank cells in the calculations.
Click an empty cell in your sheet, this will be the cell the results populate in.
In our example we have highlighted cell D5.
In the empty cell input the formula in the format of =AVERAGEIF(CellRange,"<>")
Formula Breakdown:
=AVERAGEIF: A function that returns the average of a range of data depending on varying criteria.
CellRange: The cell range is the data parameter for the calculation to be implemented on or for. In our example this will be B4:B10. A cell range can be individual cells or an entire column.
<>: This is the not equal to operator, used to define that two values are separate to one another.
In our example our formula is:
=AVERAGEIF(B4:B10,"<>")
With the formula inputted, simply press the Enter key. The formula will run and the results will populate.
In our example the result of the Average If calculation is 41.6 and is populated in the cell D5.
TIP: You can also use the AVERAGE function on a range of cells and it will automatically ignore blank cells.
Click an empty cell in your sheet, this will be the cell the results populate in.
In our example we have highlighted cell D6.
In the empty cell input the formula in the format of:
=AVERAGEIFS(Cell Range 1, Cell Range 2, “<>, Cell Range 3,"Condition")
Formula Breakdown:
=AVERAGEIFS: Function that returns the average of a range of data depending on varying criteria.
CellRange1: This is the first range of cells to be used by the Average IF calculation.
CellRange2: The second range of cells used by the Average IF calculation.
<>: This is the not equal to operator, used to define that two values are separate to one another
CellRange3: The third range of data for the calculation which will be the same as the first in our example and will be utilized in the following function.
Condition: The condition in this formula will be the greater than operator (>) followed by the number that will be the starting point for the calculation, in our example this will be ‘>10’: a value greater than 10.
In our example we want to find the average value of the cell ranges B4:B10 and C4:C10 only factoring the values based on the condition of >10 while ignoring blank cells, therefore our example formula is:
=AVERAGEIFS(B4:B10, C4:C10,"<>",B4:B10,">10")
With the formula inputted, simply press the Enter key. The formula will run and the results will populate.
The Average of the data comparison has been generated based on the parameters in the formula with the blank cells not factored into the calculation.
As can be seen in our below example, the formula result was 36.66666667.
If you enjoyed this article, you might also like our article on how to use the Google Sheets AVERAGE IF function or our article on how to set up Google Sheets AVERAGE IF not zero.
If you want to learn how to export email addresses from Google Sheets to Gmail, we also suggest checking out our detailed guide.
