Table of Contents
In Excel, the AVERAGE function can be used to calculate the average of a set of numbers. However, if the data includes zero or blank cells, these values may skew the result. To accurately calculate the average while ignoring these cells, the AVERAGE function can be combined with the IF function. This allows the user to specify the criteria for including or excluding cells in the calculation. By using this method, the average can be calculated only from the relevant data, providing a more accurate result. This can be a useful tool for analyzing data and making informed decisions based on accurate averages.
Excel: Calculate Average and Ignore Zero and Blank Cells
You can use the following formula to calculate the average in Excel while ignoring zeros and blank cells:
=AVERAGEIF(B2:B14, "<>0")
This particular formula calculates the average value in the range B2:B14 and ignores cells that are equal to zero or blank.
Note: Excel ignores blank cells by default when calculating an average.
Thus, we only need to specify in the formula that cells should not be equal ( “<>0”) to zero as well.
The following example shows how to use this formula in practice.
Example: Calculate Average & Ignore Zero and Blank Cells in Excel
Suppose we have the following dataset in Excel that shows the total sales made by various employees at a company:
If we simply used the AVERAGE() formula, we would find the average sales for all of the employees who had a non-blank value:
The average sales per employee who had a non-blank value is 3.
However, suppose we wanted to only calculate the average for employees who had a sales value that was not blank and not equal to zero.
We could type the following formula into cell D2:
=AVERAGEIF(B2:B14, "<>0")The following screenshot shows how to use this formula in practice:
This formula calculated the average by only using the values that were not blank and not equal to zero.
We can confirm that this is correct by manually calculating the average of all values that are not blank or equal to zero:
Average of Values Greater than Zero: (10+4+4+3+2+1+4+5) / 8 = 4.125.
This matches the value calculated by our formula.
The following tutorials explain how to perform other common operations in Excel:
Cite this article
stats writer (2024). How do I calculate the average in Excel while ignoring zero and blank cells?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-do-i-calculate-the-average-in-excel-while-ignoring-zero-and-blank-cells/
stats writer. "How do I calculate the average in Excel while ignoring zero and blank cells?." PSYCHOLOGICAL SCALES, 27 Jun. 2024, https://scales.arabpsychology.com/stats/how-do-i-calculate-the-average-in-excel-while-ignoring-zero-and-blank-cells/.
stats writer. "How do I calculate the average in Excel while ignoring zero and blank cells?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-do-i-calculate-the-average-in-excel-while-ignoring-zero-and-blank-cells/.
stats writer (2024) 'How do I calculate the average in Excel while ignoring zero and blank cells?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-do-i-calculate-the-average-in-excel-while-ignoring-zero-and-blank-cells/.
[1] stats writer, "How do I calculate the average in Excel while ignoring zero and blank cells?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How do I calculate the average in Excel while ignoring zero and blank cells?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.