How to calculate an average without zeros in Excel
Formula (Excel and Google Sheets)
=AVERAGEIF(B2:B7,"<>0")Use =AVERAGEIF(range,"<>0"). It averages only the cells that are not zero.
- Result on the example
- 123.75 in
E1 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in E1. Change any input and the result updates, or edit the formula to experiment.
How it works
AVERAGEIF averages only the cells that meet the condition; "<>0" means "not equal to zero". Blank cells are already ignored by every average, so this matters when zeros mean "no data" rather than a real zero.
=AVERAGEIF(B2:B7,"<>0")Result123.75
B2:B76 rows × 1 col: 120, 0, 135, 98, …A range: 6 cells in one column.
AVERAGEIF(B2:B7,"<>0")123.75AVERAGEIF: Average of values that meet a condition.- range
B2:B76 rows × 1 col: 120, 0, 135, 98, … - criteria
"<>0"
- range
In Google Sheets
The same formula works in Google Sheets: =AVERAGEIF(B2:B7,"<>0"). We ran it in Google Sheets mode on the same data and it returns 123.75.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUM(B2:B7)/COUNTIF(B2:B7,"<>0") | 123.75 | The same average built from its parts. |
| =AVERAGE(B2:B7) | 82.50Different result | The plain average counts the zeros. Includes the zero weeks: 82.5. |
Common mistakes
- If every value is zero, AVERAGEIF returns #DIV/0!. Wrap it in IFERROR if that can happen.
- Decide whether zeros are real data. A shop that sold nothing in a week did sell zero; a store that was closed has no data.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Calculate an average without zeros
E1, average the units sold, leaving out the weeks with 0.Questions
How do I average only positive numbers?
Use =AVERAGEIF(B2:B7,">0"), which also leaves out negatives.
How do I ignore errors in an average?
Use =AGGREGATE(1,6,B2:B7), which skips error values.
Checked by running on 2026-10-29: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.