Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to average ignoring zeros in Excel and Google Sheets
Topic:AVERAGE & central tendencyExcel & Google Sheets
=AVERAGEIF(B2:B6,"<>0")✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| Month | Sales |
| Jan | 0 |
| Feb | 150 |
| Mar | 0 |
| Apr | 200 |
| May | 175 |
=AVERAGEIF(B2:B6,"<>0")→175
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Select the output cell.
- 2Type =AVERAGEIF(.
- 3Select the range of values, e.g. B2:B6.
- 4Enter the criterion "<>0" to exclude zeros.
- 5Close the parenthesis and press Enter.
Tips
- "<>0" also excludes cells that contain the text "0" — verify data is stored as numbers.
- AVERAGEIF is available in Excel 2007+ and all Google Sheets versions.
- If you also want to exclude blanks (they are already excluded by AVERAGE/AVERAGEIF), no extra steps are needed.
- To exclude zeros and negative numbers, use =AVERAGEIF(B2:B6,">0").
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
What if I want to exclude zeros and blanks?
AVERAGEIF already ignores blanks. Just use =AVERAGEIF(B2:B6,"<>0") and both zeros and blanks are excluded.
Can I use AVERAGEIFS to exclude zeros and apply another condition?
Yes. =AVERAGEIFS(B2:B6,B2:B6,"<>0",A2:A6,"North") averages non-zero values in the North region.
More on AVERAGE & central tendency
See all →How-to
How to calculate an average
Use the AVERAGE function to calculate the arithmetic mean of a range of numbers in Excel or Google Sheets.
How-toHow to average ignoring blank cells
AVERAGE naturally ignores blank cells; use AVERAGEIF with "<>" to also ignore text placeholders like dashes.
How-toHow to average with a condition
Use AVERAGEIF to compute the mean of values in one column where a corresponding column meets a specified criterion.
How-toHow to calculate a moving average
Compute a rolling average for each row using AVERAGE with a fixed-window OFFSET or INDEX formula anchored to each row.
How-toHow to find the median of a range
Use the MEDIAN function to find the middle value in a dataset — the value that half the data falls above and half below.
How-toHow to find the most common value
Use MODE for numbers or COUNTIF-based logic for text to find the value that appears most frequently in a dataset.