Excel Average Formula: Zeros, Blanks and Weights

Use AVERAGE on the intended numeric range

If A2:A5 contains 8, 12, 15 and 25, enter =AVERAGE(A2:A5) in another cell. The sum is 60 and there are four numbers, giving an arithmetic mean of 15. The colon selects the complete range between its endpoints. Check that the range does not include a summary row or omit a final observation. In the local Average Calculator, enter 8,12,15,25 to verify the same mean from the underlying values without importing a workbook.

Distinguish an empty cell from a recorded zero

For a referenced range containing 10, 20 and an empty cell, AVERAGE uses the two numbers and returns 15. Replacing the empty cell with zero includes a third observation and gives 10. A blank may represent an unrecorded observation, while zero may represent an observed quantity of none. These meanings are not interchangeable. Hiding zero values through display settings does not remove them from the calculation. Decide the data meaning before replacing blanks or zeros during cleanup.

Keep text and error handling deliberate

Text and logical values inside a referenced range are ignored by AVERAGE, but numeric zeros are included. A number stored as text can therefore be omitted unexpectedly even when it looks like the other entries on screen. Inspect imported columns and convert valid numeric text consistently when it should be counted. An error value in the input range can propagate an error rather than being silently skipped. The local numeric-list form instead rejects nonnumeric tokens, so its input-cleaning behavior is not identical to an Excel range.

Exclude zeros only when the analysis calls for it

For the data 0, 8 and 10, the ordinary mean is 6. If zero is a genuine observation, excluding it changes the statistic and gives 9. A criteria-based formula such as =AVERAGEIF(A2:A4,"<>0") can implement an explicitly chosen nonzero-only calculation. Label that result accordingly rather than presenting it as the mean of all three observations. Missing values coded as zero should first be identified from the data specification; a formula cannot infer which zeros are measurements and which are placeholders.

Use weights when contributions differ

Suppose values 70 and 90 have weights 2 and 3. Their weighted mean is (70×2+90×3)/(2+3) = 82. With values in A2:A3 and weights in B2:B3, =SUMPRODUCT(A2:A3,B2:B3)/SUM(B2:B3) expresses that calculation. The unweighted AVERAGE(A2:A3) would return 80. The local Average Calculator has no separate weights column, although entering repeated observations 70,70,90,90,90 checks this integer-weight example. For general weights, use a weighted-average workflow instead.

Combine group means using their counts

If one group of two observations has mean 10 and another group of eight has mean 20, the combined mean is (2×10+8×20)/10 = 18. Averaging the two group means directly gives 15 and treats the unequal groups as equally large. Keep each group's count or original observations so the combined calculation can be reconstructed. Rounded group means may introduce a small discrepancy compared with using the raw totals; retain unrounded summaries when the final combined precision matters.

Compare the mean with the distribution

For 2, 3, 4 and 31, the mean is 10 while the median is 3.5. The large final value pulls the mean upward. This does not make either statistic wrong, but it changes what a typical-value statement communicates. Inspect the range and unusual values before interpreting an average, and distinguish data correction from removing a valid extreme observation. Format the final result to an appropriate number of decimals while retaining the source values and formula for reproducibility.

Check the number of included observations

For a numeric range, COUNT can check how many entries contribute to the mean. Compare that count with the expected dataset size before interpreting an unexpectedly high or low average.

Related guides

Calculate an Average and Check the Denominator

Calculate arithmetic means from raw numbers, distinguish zero from missing data, and handle weighted groups, outliers and average rates correctly.

Basic Excel Formulas with Worked Cell Examples

Create arithmetic formulas in Excel, use cell references and parentheses, and distinguish a formula result from text or number formatting.

Formula to Show Percentage Increase in a Worksheet

Write the percentage-increase formula, display the result correctly in a spreadsheet, and distinguish a decimal ratio from a percentage-formatted cell.