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.
Average IF Not Blank Using the AVERAGEIF Function
In the demonstration below we will use the Average IF function on one and two data sets, not including blank cells in the calculations.
1. Select an empty cell in your spreadsheet
Click an empty cell in your sheet, this will be the cell the results populate in.
In our example we have highlighted cell D5.
2. Enter the formula =AVERAGEIF(CellRange,"<>")
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,"<>")
3. Press Enter to see the results
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.
Average IF Not Blank Using the AVERAGEIFS Function
1. Select an empty cell in your spreadsheet
Click an empty cell in your sheet, this will be the cell the results populate in.
In our example we have highlighted cell D6.
2. Enter the formula =AVERAGEIFS(CellRange1, CellRange2, “<>, CellRange3,"Condition")
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")
3. Press Enter to populate the results
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.
Recommended articles
More resources
