The AVERAGEIF function in Google Sheets doesn't have a specific "not zero" version, but it can be used to calculate the average of numbers that are not zero by setting an appropriate criterion. This is a useful technique for analyzing datasets where zero might represent missing or irrelevant data.
AVERAGEIF(range, criterion, [average_range])
range is the range of cells that you want to evaluate with the criterion.
criterion is the condition that the cells in the range must meet to be included in the average. This can be a number, expression, or text that defines which cells will be averaged.
[average_range] (optional) is the actual set of cells to average. If omitted, the cells in range are used for both evaluating the criterion and calculating the average.
Follow the steps below to use the AVERAGE IF not 0 function in Google Sheets to calculate the average of a range of numbers while excluding zero values.
Look at your spreadsheet and find the range of cells containing the numbers you want to average. For example, if your numbers are in the first column from rows 2 to 11, your range is A2 to A11.
Click on an empty cell where you would like the average result to be displayed. This is where the formula will go and where the answer will appear. Choose a cell that is not in your data range to avoid overwriting any data.
In the selected cell, type the formula: =AVERAGEIF(A2:A11, "<>0"). This formula instructs Google Sheets to calculate the average of numbers in the range A2 to A11 but to ignore any cells that contain a zero. The "<>0" part of the formula is what tells Google Sheets to exclude zero values.
After typing the formula, press the Enter key. This will cause Google Sheets to execute the formula. The program will calculate the average of the numbers in your specified range and excludes any cells that contain zero.
Look at the cell where you entered the formula. It should now show the calculated average of your selected numbers, with zero values excluded from the calculation. If the cell shows an error or unexpected result, double-check the formula for any typos or incorrect range references.
We hope that you now have a better understanding of how to set up the Google Sheets AVERAGE IF not zero function.
If you enjoyed this article, you might also like our article on how to set up AVERAGE IF not blank in Google Sheets or our article on how to check if a cell is empty in Google Sheets.
If you want to learn how to attach Google Sheets to emails, we also suggest checking out our detailed guide.
