# How to calculate an average without zeros in Excel

Use =AVERAGEIF(range,"<>0"). It averages only the cells that are not zero.

**Excel:** `=AVERAGEIF(B2:B7,"<>0")` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D |
| --- | --- | --- | --- |
| Week | Units sold |  | Average (no zeros) |
| W1 | 120 |  |  |
| W2 | 0 |  |  |
| W3 | 135 |  |  |
| W4 | 98 |  |  |
| W5 | 0 |  |  |
| W6 | 142 |  |  |

Formula in E1: `=AVERAGEIF(B2:B7,"<>0")`

Result: 123.75

## 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.

## Other ways

- `=SUM(B2:B7)/COUNTIF(B2:B7,"<>0")`: The same average built from its parts.
- `=AVERAGE(B2:B7)`: 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.

## 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.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-29.

Source: https://spreadsheetpractice.com/formulas/average-excluding-zeros