Excel has special functions to calculate the average of the number in a range of cells and also calculate the average of cells based on specified criteria, like AVERAGE and AVERAGEIF functions. But there are situations where cells in a range are Blank or may contain zeros, so it may affect the result. So you need to take Excel average without zeros and average if not blank. In this case, you need to use AVERAGEIF function to average cells based on criteria.
The AVERAGEIF function in Excel
AVERAGEIF function averages cells based on supplied criteria in Excel as per its syntax. The syntax of AVERAGEIF function is as follows;
=AVERAGEIF [range, criteria, [average_range]]
Here,
range – This argument contains the range of cells on which criteria are tested.
criteria – This argument contains the condition to determine which cells to average. It may contain numeric or text values, a logical expression, cell reference or other functions as the condition to meet.
average_range – This argument consists of the range of cells that contain numbers you want to average. It is the optional argument and if you omit this argument in function then function averages cells given in range argument.
Ignore zeros when finding the average
In this case, as you need to average ignoring zeros in a range argument, so you do not need to supply average_range argument in the AVERAGEIF function here. You need to supply only range argument and criteria argument to find the average.
In the criteria argument, you need to test the criteria “Not Equal to Zero”. This is done by using logical expression Not Equal To [] with Zero, enclosed in double quotation marks [“”].
Ignore Blanks When Finding the Average
Remember that like AVERAGE function, AVERAGEIF function automatically ignores Blank cells and cells containing text values. So you do not need to make any special arrangements to average ignoring blanks and text values in the range. But where cells contain zeros, you need to use AVERAGEIF function based on criteria “Not Equal to Zero.”
In this example you need Excel average ignoring Zeros and Blank cells and how it is different from the simple average of these cells. Suppose you have a range of cells A1:A11 that contains numbers, zeros, and blank cells. By using the AVERAGEIF function with criteria expression Not Equal to Zero [“0”] you will average cells ignoring zero and Blank values. The formula, in this case, would be;
=AVERAGEIF[A1:A11,"0"]
This formula eliminates zero values as a result of the criteria expression and Blank cells as default functionality of AVERAGEIF function, so it only counts cells in Excel average without zeros and average if not blank.
As you are aware that AVERAGE function by default ignores Blank cells, but it does not ignore zeros, so that why its result is different from the AVERAGEIF function ignoring zeros and blanks. You can see the difference of results by averaging the same range of values A1:A11 as a result of these two functions, such as;
=AVERAGE[A1:A11]
AND
=AVERAGEIF[A1:A11,"0"]
Still need some help with Excel formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free.
Are you still looking for help with the Average function? View our comprehensive round-up of Average function tutorials here.
The goal of this example is to verify input before calculating a result. The key point to understand is that any valid formula can be substituted. The SUM function is used only as an example. The logic can also be adjusted in many ways to suit the situation.
In the example shown, we are using the IF function together with the COUNT function. The criteria is an expression based on the COUNT function, which only counts numeric values:
COUNT[C5:C7]=3 // returns TRUE or FALSE
As long as the range contains three numbers [i.e. all 3 cells are not blank] the result is TRUE and IF will run the SUM function. If not, result is FALSE and IF returns an empty string [""]. Since C7 has no value in the screen above, the formula shows no result.
There are many ways to check for blank cells, and several options are explained below.
With COUNTBLANK
The COUNTBLANK function counts empty cells in a range, so we can write a slightly more compact formula like this:
If COUNTBLANK returns any number except zero, the IF function will evaluate as TRUE, and return nothing [""]. If COUNTBLANK returns zero, IF evaluates as FALSE and returns the sum.
With ISBLANK
In the example shown, input cells are all in the same contiguous range. In cases where cells are not together, you can a formula like this:
This example takes a literal approach with the ISBLANK function. Because we want to check all three cells at the same time, we need to use ISBLANK three times inside the OR function. This is the logical test inside IF:
When OR returns TRUE [at least one cell is empty], IF returns an empty string [""]. When OR returns FALSE [no cells are blank], IF runs the SUM function and returns the result:
With logical operators
The ISBLANK function can be replaced with standard logical operators like this:
=IF[OR[C5="",C6="",C7=""],"",SUM[C5:C7]]
Alternately, we can combine the not equal to operator [] with AND function like this:
=IF[AND[C5"",C6"",C7""],SUM[C5:C7],""]
Notice the SUM function has been moved to the TRUE result. It will run only if C5 and C6 and C5 are not empty.
With COUNTA
Finally, you can use the COUNTA function to check for numeric or text input:
As long as the range C5:C5 contains three values [numbers or text], the result will be TRUE and the SUM function will run. This doesn't really make sense for the example shown [which requires numeric input] but it can be used in other situations.