If a zero means a reading was never received, replace that placeholder with a genuinely blank cell and use =AVERAGE(B2:B8). In the worked example, the result is 126 units per reported day.
Excel Average Too Low? Check Whether Zeros Mean Missing Daily Data
If a zero means a reading was never received, replace that placeholder with a genuinely blank cell and use =AVERAGE(B2:B8). In the worked example, the result is 126 units per reported day.
A monthly production average can look unexpectedly low when staff enter 0 for a day with no submitted reading. Excel is not making a calculation error. It sees a numeric zero and includes it in the average.
The important reporting decision is whether that zero represents a real no-output day or a missing measurement. A real zero belongs in the KPI. A missing measurement should normally be blank, with a separate note or status field if someone needs to investigate it.
Quick Answer: Excel does not ignore real zeros
Excel average ignores zeros only when you deliberately exclude them. Microsoft states that AVERAGE ignores empty cells and text in referenced ranges, but includes cells containing zero. That distinction explains why a visible zero can pull down a monthly KPI while a blank cell does not. See Microsoft’s documentation for the AVERAGE function.
Use this simple rule before changing any formula:
| What the daily cell means | What to enter | How the average should treat it |
|---|---|---|
| Production was measured and was genuinely zero | 0 |
Include it. |
| No measurement was received | A blank cell | Ignore it. |
| The result is unknown and requires follow-up | A blank cell plus a status note | Ignore it, while preserving the audit trail. |
Worked Example: a low monthly production average
In the monthly production example, cells B2:B8 contain 120, 135, 0, a blank cell, 110, 140, and 125.
| Cell | Day | Units produced | Interpretation |
|---|---|---|---|
| B2 | Monday | 120 | Reported reading |
| B3 | Tuesday | 135 | Reported reading |
| B4 | Wednesday | 0 | Placeholder entered because no reading arrived |
| B5 | Thursday | Blank | No reading received |
| B6 | Friday | 110 | Reported reading |
| B7 | Saturday | 140 | Reported reading |
| B8 | Sunday | 125 | Reported reading |
First, leave B4 as zero and enter the normal average formula in B10:
=AVERAGE(B2:B8)The result is 105. Excel adds the six numeric values: 120 + 135 + 0 + 110 + 140 + 125 = 630. It then divides 630 by 6. The blank in B5 is ignored, but the zero in B4 is a number, so it is included in the count.
Now confirm with the process owner that Wednesday was not measured rather than measured as zero. Clear B4 so the cell is genuinely empty. Keep the same formula in B10:
=AVERAGE(B2:B8)After the placeholder zero is treated as missing, =AVERAGE(B2:B8) returns 126, and the temporary =AVERAGEIF(B2:B8,"<>0") calculation also returns 126. The total remains 630, but Excel now divides it by the five reported values.
This is usually the safest solution because the source data records the real business condition: there was no measurement. Do not replace a missing value with a dash, a space, or a text note in the production column. Keep the numeric column numeric, and put explanations in a separate status or comments column.
Temporary Fix When You Cannot Edit the Source
Some teams must leave an imported export or controlled source workbook unchanged. In that case, calculate a separate reporting metric while the data owner resolves the missing reading.
For a temporary reporting calculation that must leave the source untouched, enter =AVERAGEIF(B2:B8,"<>0") in B10.
=AVERAGEIF(B2:B8,"<>0")The criterion "<>0" means “not equal to zero.” This formula averages the numeric values in B2:B8 except zero, so it returns 126 for the sample range. Label the result clearly as Average per reported day, not simply “monthly average.” The label makes the denominator clear to anyone reading the report.
This formula is a reporting workaround, not a universal replacement for AVERAGE. If the range contains valid zero-output days, it will exclude them too. For reports that also need criteria such as location, shift, or date, an AVERAGEIFS formula with multiple criteria can keep the reporting rules explicit.
Audit the Range Before Publishing the KPI
Before presenting the result, check that the count of numeric observations matches the number of days that actually reported data. In B11, enter:
=COUNT(B2:B8)With B4 still set to zero, this returns 6. After you clear B4, it returns 5. The count is useful beside the average because it exposes the number of numeric cells included in the calculation. A KPI of 126 based on five reported days tells a different story than a KPI of 126 based on seven calendar days.
- Select the source range, such as B2:B8.
- Check the formula bar for a few apparent blanks and zeros.
- Use
=COUNT(B2:B8)to confirm the number of numeric observations. - Ask the process owner whether each zero means actual no output or an unsubmitted reading.
- Use the correct formula only after the data definition is agreed.
A truly empty cell is ignored by AVERAGE, but a cell containing 0 is included in both the total and the count. That is the specific difference that changes the sample result from 105 to 126.
Common Problems and Their Remedies
| Symptom | Likely cause | Practical remedy |
|---|---|---|
| The revised average looks too high | Real zero-output days were excluded. | Restore those zeros and use ordinary AVERAGE. |
| Some visible numbers do not affect the result | The imported values are stored as text. | Convert them to numbers before relying on the KPI. See how to fix numbers stored as text. |
| Zeros appear blank on the worksheet | A display setting or custom number format hides them. | Select the cell and inspect the formula bar and number format. |
| The source export must remain unchanged | The data is controlled or supplied externally. | Use a separate, labelled reporting formula and document the exclusion rule. |
Hiding zeros with a worksheet display setting changes what people see, not the zero values used by the calculation. Microsoft explains that worksheet settings and custom formats can conceal zero values while retaining the underlying value; review its guidance on displaying or hiding zero values before treating a blank-looking cell as missing data.
Boundary Cases to Check
This method is not appropriate when zero is a legitimate no-output day, because excluding that zero would overstate the operational average. For example, if a plant was scheduled to operate and measured production was zero, retain 0 and use =AVERAGE(B2:B8).
Negative values are numeric observations too. If A1:A3 contains -5, 0, and 5, then =AVERAGE(A1:A3) returns 0. A negative adjustment should not be excluded merely because it lowers the result; establish whether it is a valid return, correction, or reversal first.
A formula that returns an empty string ("") is not the same as a physically empty input cell for every downstream check, so inspect the formula before assuming a missing reading. If a blank-looking result comes from another formula, trace that source rather than changing the KPI blindly.
If a supposed number is stored as text, AVERAGE ignores it in a referenced range; convert the value before relying on the KPI. If the issue starts with an upstream formula referring to an empty input, use this guide for an Excel formula that refers to a blank cell to investigate the dependency.
Compatibility and a Reliable Fallback
Microsoft documents AVERAGE for Excel for Microsoft 365, Excel 2024, Excel 2021, Excel 2019, and Excel 2016, including the listed Mac editions. This approach does not require dynamic-array functions. The core method—using blanks for missing readings and AVERAGE for the numeric range—is also a practical option in Excel for the web when the workbook’s existing functions are available there.
The most reliable fallback is a data-entry convention rather than a more complicated formula: use blanks only for unreported readings, retain legitimate zeros, and keep a separate status column for explanations. That makes the average easier to audit, protects the meaning of the KPI, and prevents a formatting choice from quietly changing a reporting decision.
Sources
Worked example details and boundary cases
Method
For a temporary reporting calculation that must leave the source untouched, enter =AVERAGEIF(B2:B8,"<>0") in B10.
Expected result
After the placeholder zero is treated as missing, =AVERAGE(B2:B8) returns 126, and the temporary =AVERAGEIF(B2:B8,"<>0") calculation also returns 126.
A formula that returns an empty string ("") is not the same as a physically empty input cell for every downstream check, so inspect the formula before assuming a missing reading.