In Google Sheets, when you use the average formula to average numbers, and those numbers have one or more zeros in the list, the AVERAGE function will include those zeros.
To solve this problem, you need to use the AVERAGEIF function.
Calculate the Average but Exclude Zeros
Follow these steps:
- First, enter the AVERAGEIF function in a cell.
- Next, specify the range address where you have values.
- Now, enter the criteria (“<>0”) to exclude zeros while calculating the average.
- And, in the third argument, again specify the range address where you have values (you can skip this step if you want).
- In the end, close the function and hit enter to get the result.
=AVERAGEIF(A1:A5,"<>0",A1:A5)As I said, you can skip specifying the average_range argument as optional. The formula will skill work.
