Quick answer: `=AVERAGE(B2:B7)` includes numeric zeros, so 12, 15, 0, 18, 0, and 21 produce 11. Keep that result if both zeros are genuine zero-stock days. If they are missing reports, correct the source data so missing observations are blank or kept out of the reporting range; averaging the four confirmed observations, 12, 15, 18, and 21, produces 16.5.
Quick answer: =AVERAGE(B2:B7) includes numeric zeros, so 12, 15, 0, 18, 0, and 21 produce 11. Keep that result if both zeros are genuine zero-stock days. If they are missing reports, correct the source data so missing observations are blank or kept out of the reporting range; averaging the four confirmed observations, 12, 15, 18, and 21, produces 16.5.
A lower-than-expected inventory, sales, or activity average often looks like a formula problem. Usually, it is a data-meaning problem instead. Excel sees a numeric 0 as a recorded value. It cannot know whether your team used that zero to mean “there was none” or “we did not receive the report.”
Excel AVERAGE includes zeros by design. That behavior is correct when a zero is a real business outcome. It becomes misleading only when missing observations have been entered as zero placeholders.
Quick Fix: Decide What Each Zero Means
Before changing a report, ask one direct question: Was the result actually zero, or was the value never reported?
- Actual zero: Keep the zero in the source range and use the normal average.
- Missing observation: Do not use zero as a placeholder. Leave the measurement blank or maintain a separate reporting range containing only confirmed observations.
Microsoft states that AVERAGE ignores empty cells in a referenced range but includes cells containing zero. That distinction is documented in Microsoft’s AVERAGE function guidance.
Worked Example: Why the Result Is 11
Imagine a warehouse tracks daily available units. The following table is the raw export received by the reporting team. Wednesday and Friday show zero, but the operations manager later confirms that those two daily counts were not reported. They were not verified zero-stock days.
In the sample availability table, cells B2:B7 contain 12, 15, 0, 18, 0, and 21. The values total 66 across six entered numbers.
| Cell | Day | Available units | Meaning in the raw export |
|---|---|---|---|
| B2 | Monday | 12 | Confirmed count |
| B3 | Tuesday | 15 | Confirmed count |
| B4 | Wednesday | 0 | Missing count entered as a placeholder |
| B5 | Thursday | 18 | Confirmed count |
| B6 | Friday | 0 | Missing count entered as a placeholder |
| B7 | Saturday | 21 | Confirmed count |
Enter this formula in the report cell:
=AVERAGE(B2:B7)=AVERAGE(B2:B7) returns 11. Excel adds 66 and divides by six numeric entries. Both zeros are counted because they are numbers, so the calculation is 66 ÷ 6.
If Wednesday and Friday were genuine zero-stock days, 11 is the correct average availability for the six-day period. Do not try to make the result higher. The two zero days are part of what happened operationally.
Fix Missing Data at the Source or in a Clean Reporting Range
Now use the manager’s confirmation: Wednesday and Friday were missing observations, not zero availability. The best correction is to preserve that distinction in the workbook. Keep the raw export for traceability, then create a cleaned reporting range with only confirmed observations.
| Cell | Day | Confirmed available units |
|---|---|---|
| D2 | Monday | 12 |
| D3 | Tuesday | 15 |
| D4 | Thursday | 18 |
| D5 | Saturday | 21 |
Then enter:
=AVERAGE(D2:D5)=AVERAGE(B2:B7) returns 11. After the two confirmed missing observations are excluded from the reporting range, =AVERAGE(D2:D5) returns 16.5.
The second result uses 66 ÷ 4. It is not a replacement for the first result; it answers a different, clearly defined question: What was average availability on days with a confirmed count?
This approach is easier to audit than silently treating every zero as missing. A colleague can inspect the raw data, the confirmed reporting data, and the stated reporting rule. For ongoing reporting, add a separate status field such as Confirmed, Missing, or Not applicable. That keeps status text out of the numeric measure column.
When Zeros Must Stay in the Average
Removing zeros can make a report appear better than the underlying operation. Keep numeric zero values when they describe a real result, including these cases:
- A store was open and made zero sales.
- A warehouse count confirms zero units on hand.
- A service desk was staffed and received zero calls.
- A production line operated during a shift and produced zero acceptable units.
Limitation: do not remove a zero merely because it lowers an average. A zero must remain in the calculation when it represents genuine zero stock, zero sales, or zero activity; excluding it changes the business question and can overstate performance.
Blanks, Zeros, and Hidden Zeros Are Different
| What is in the cell? | Does AVERAGE use it? | Recommended interpretation |
|---|---|---|
A number such as 18 |
Yes | A confirmed measurement |
Numeric 0 |
Yes | A confirmed zero result |
| A truly empty cell | No | No observation was received |
Text such as Pending |
No | A status that is better stored in its own column |
A truly blank cell is different from a cell containing 0: AVERAGE ignores an empty referenced cell but includes a numeric zero. If a formula unexpectedly pulls through a blank-looking value, review the difference between empty cells, zero values, and formula results with this guide to an Excel formula that refers to a blank cell.
Also, hiding zeros is only a visual choice. Hiding zero values changes the worksheet display only. It does not remove numeric zeros from AVERAGE calculations. Use display formatting only when readers need a cleaner worksheet, not when the source data requires correction. See the related guide on how to hide zero values in Excel without confusing what is shown with what is calculated.
Boundary Cases to Check Before You Publish
If B2:B7 contains only numeric zeros, =AVERAGE(B2:B7) returns 0. That is the correct result for six recorded zero outcomes, not evidence that Excel ignored the data.
A single-cell range containing zero, such as =AVERAGE(B2:B2) when B2 is 0, returns 0 because the one numeric value is included.
For a simple normal check, enter 12 in A1, 15 in A2, and 18 in A3. The formula below returns 15:
=AVERAGE(A1:A3)This small example confirms the basic rule: Excel adds the numbers and divides by the number of numeric values in the range.
Compatibility and Practical Fallback
AVERAGE works in Excel for Microsoft 365, Excel for the web, Excel 2024, Excel 2021, Excel 2019, and Excel 2016. If a workbook must remain easy to audit across versions, keep a separate cleaned reporting range of confirmed observations and use the same AVERAGE formula on that range.
This fallback is deliberately simple. It does not depend on newer functions, hidden calculation rules, or a formula that guesses whether zero means missing. It also gives reviewers a visible place to confirm which days were included. If the report needs both measures, show them with clear labels: Average across all calendar days and Average across confirmed observation days.
Reporting Checklist
- Confirm the business meaning of every zero before changing the average.
- Use the raw range when zeros are genuine operational results.
- Use a cleaned reporting range only for observations confirmed as reported and valid.
- Keep missing-data status visible rather than disguising it as zero.
- Label the average so report readers know whether it covers every day or only confirmed observation days.
Worked example details and boundary cases
Example inputs
In the sample availability table, cells `B2:B7` contain 12, 15, 0, 18, 0, and 21. The values total 66 across six entered numbers.
Method
Enter `=AVERAGE(B2:B7)` in the report cell to average all six entered numbers. For a report that treats Wednesday and Friday as missing observations, use a cleaned reporting range containing only the confirmed values 12, 15, 18, and 21, then enter `=AVERAGE(D2:D5)`.
Expected result
`=AVERAGE(B2:B7)` returns 11. After the two confirmed missing observations are excluded from the reporting range, `=AVERAGE(D2:D5)` returns 16.5.
Compatibility
`AVERAGE` works in Excel for Microsoft 365, Excel for the web, Excel 2024, Excel 2021, Excel 2019, and Excel 2016. If a workbook must remain easy to audit across versions, keep a separate cleaned reporting range of confirmed observations and use the same `AVERAGE` formula on that range.
If `B2:B7` contains only numeric zeros, `=AVERAGE(B2:B7)` returns 0. That is the correct result for six recorded zero outcomes, not evidence that Excel ignored the data.
A single-cell range containing zero, such as `=AVERAGE(B2:B2)` when `B2` is 0, returns 0 because the one numeric value is included.
A truly blank cell is different from a cell containing 0: `AVERAGE` ignores an empty referenced cell but includes a numeric zero.
Hiding zero values changes the worksheet display only. It does not remove numeric zeros from `AVERAGE` calculations.