Quick answer: =AVERAGE(B2:B8) ignores genuinely empty cells but counts every numeric 0. Keep 0 for a confirmed no-sales day, but leave an unreported daily value blank until it is corrected.
An average can look unexpectedly low even when Excel is calculating it correctly. The common reporting problem is that a worksheet uses 0 for two different situations: a day when activity was measured and was genuinely zero, and a day when no one supplied the result.
Excel average includes zeros. The fix is not to remove every zero that lowers the result. First decide what each input means, then use AVERAGE on data that follows that definition. This guide uses a small weekly order-value report so you can reproduce the calculation and explain it clearly to a manager or colleague.
Quick Answer: Excel Average Includes Zeros
Excel counts a cell containing the numeric value 0 in an AVERAGE calculation. Excel ignores a genuinely empty cell in a referenced range. Therefore, a confirmed zero-order day belongs in the average, while an unreported day should remain empty only if your reporting rule says that incomplete observations are excluded.
This distinction matters because changing a zero to a blank changes the denominator. It is a reporting decision, not merely a cosmetic formula adjustment.
Worked weekly order-value example
Suppose you prepare a weekly order-value report. Wednesday and Saturday were confirmed no-orders days. Thursday has not been reported because the daily source file is late. Record those situations differently.
In the sample data table, enter 240 in B2, 180 in B3, 0 in B4, leave B5 empty, enter 310 in B6, 0 in B7, and 270 in B8. B4 and B7 are confirmed no-orders days; B5 is an unreported day.
| Cell | Day | Order value | Reporting meaning |
|---|---|---|---|
| B2 | Monday | 240 | Recorded value |
| B3 | Tuesday | 180 | Recorded value |
| B4 | Wednesday | 0 | Confirmed no orders |
| B5 | Thursday | (empty) | Daily file not received |
| B6 | Friday | 310 | Recorded value |
| B7 | Saturday | 0 | Confirmed no orders |
| B8 | Sunday | 270 | Recorded value |
Type a useful label such as Average daily order value in A10. Then select B10 and enter this formula:
=AVERAGE(B2:B8)In B10, enter =AVERAGE(B2:B8). The formula adds the six numeric cells, including the two zeros, and divides by six; it ignores the empty cell B5.
The expected result in B10 is 166.67 when formatted to two decimal places, because (240+180+0+310+0+270)/6 equals 166.6667.
The calculation is based on six reported numeric observations, not seven calendar days. That may be appropriate for an interim report, but it should be disclosed. If Thursday is later confirmed as a no-orders day, enter 0 in B5. The same formula will then divide by seven and return 142.86. If Thursday later has an order value, enter that value instead and allow the formula to recalculate.
Why zeros affect AVERAGE but blanks do not
AVERAGE returns an arithmetic mean: it adds numeric values and divides by the count of numeric values. Zero is a numeric value. It contributes nothing to the sum, but it does add one to the count. An empty cell contributes neither an amount nor a counted observation.
| Cell content | Included by AVERAGE? | Appropriate meaning |
|---|---|---|
0 | Yes | The activity was measured and the result was zero. |
| Empty cell | No | The value is unknown, late, or not applicable under the report definition. |
A number, such as 310 | Yes | The reported result is valid. |
An error, such as #N/A | No usable average result | The source data or formula needs investigation. |
Microsoft’s AVERAGE function documentation states that empty cells in a referenced range are ignored and cells containing zero are included. This is why =AVERAGE(B2:B8) returns 166.67 in the example rather than treating the two zero values as missing.
Make the reporting decision before changing the formula
Use one practical question for each zero: Was the measure actually observed and confirmed as zero? If yes, retain the numeric zero. It is part of the performance record and should lower the average when appropriate.
- Confirmed no sales or no orders: retain
0. The period was observed, and its result was zero. - Missing extract, late entry, or failed import: leave the source cell empty while you obtain or repair the value. Do not substitute zero just to complete a table.
- Not applicable: use an empty cell only when the metric genuinely does not apply, according to the report’s documented rules.
- Unknown business meaning: pause publication and ask the data owner. A formula cannot supply missing operational context.
A blank is not a way to make performance look better. It excludes an observation. If management expects a seven-day average regardless of data availability, correct the missing source record rather than quietly publishing an average based on fewer days. If a formula unexpectedly returns a result from an apparently empty input, review the reference with our guide to an Excel formula that refers to a blank cell.
Check whether a zero is hidden rather than empty
A cell that looks blank in the grid may still contain numeric zero. Excel can hide zeros through worksheet display settings or a custom number format. The value remains 0 underneath, so AVERAGE continues to count it.
Select the suspicious cell and read the formula bar. If the formula bar shows 0, treat it as a zero for calculation purposes even if the cell display looks empty. Microsoft describes these display options in its guidance on displaying or hiding zero values.
For input validation, temporarily use the General number format on the data cells and make zeros visible. Our guide to hiding zero values in Excel explains the display choices. Hiding zeros may be suitable for presentation, but it is risky while you are deciding whether records are complete.
Boundary cases and remedies
If every day in the period was reported and two days had no sales, retain both numeric zeros: =AVERAGE(B2:B8) should include them because they are valid observations.
If a daily value is unavailable rather than zero, leave the cell empty or correct the source record; entering 0 lowers the average as though the day had a confirmed zero result.
If zero values are hidden by a worksheet setting or a custom number format, they can look blank while AVERAGE still includes them; select the cell and inspect the formula bar.
If the input range contains an error such as #N/A, correct the underlying input or import problem first; an error is not evidence that the day should be treated as zero or blank.
A simple boundary check can also confirm the basic rule. Enter 0 in A1, then enter =AVERAGE(A1:A1) in another cell. The result is 0, because a single numeric zero is a valid value, not an empty observation.
Compatibility, fallback, and limitation
This calculation uses the standard AVERAGE function, documented by Microsoft for Excel for Microsoft 365, Excel 2024, Excel 2021, Excel 2019, and Excel 2016. No dynamic-array function is required; if a displayed blank is in doubt, apply the General number format and inspect the formula bar.
The formula syntax is the same basic pattern in the listed versions: enter =AVERAGE(B2:B8) in a result cell and press Enter. A workbook with hidden zeros may look different between users because display settings can be worksheet-specific, so validate the underlying input cells rather than relying only on what is visible.
A formula cannot determine whether zero means no sales, not reported, or a value hidden by formatting. Establish that business meaning in the source data before publishing the average.
Troubleshooting an unexpectedly low average
| Symptom | Likely cause | Reliable action |
|---|---|---|
| The average is lower than expected | Missing days were entered as zero | Check the source records. Replace only truly unreported values with empty cells, then disclose the incomplete period. |
| Cells appear blank but affect the average | A zero is hidden by formatting | Select the cell, inspect the formula bar, and temporarily apply General formatting. |
| The formula returns an error | An input contains #N/A, #VALUE!, or another error | Trace and correct the input or import issue instead of masking the error in the average. |
| People disagree about the result | Zero and blank are not defined in the report | Add a note stating what zero means, what blank means, and how many reported days were included. |
The dependable workflow is straightforward: validate whether each day is reported, show hidden zeros while reviewing the range, use 0 only for confirmed zero results, and calculate the average after those choices are documented.