You work out the final grade of twenty students and the class mean lands a full point below what you expected. It turns out three students missed an exam and carry a 0 that drags everything down.
How to use Average Formula step by step
1. Choose the kind of average
Plain, with a condition, ignoring zeros or weighted. The weighted one asks for a range of weights.
2. Give the ranges
Values go in one range; for the weighted average, the weights in another of the same size. With a condition, add the range being tested.
3. Decide on rounding and copy
You can round to 0, 1 or 2 decimals inside the formula itself, with no cell format involved.
The Step Count: Traditional Friction vs. KorexTools
Computing the mean and cleaning zeros by hand (4 steps)
- Type AVERAGE over the column.
- Notice the figure is lower than expected.
- Delete the zeros or filter them out.
- Lose the original data when deleting.
With the KorexTools “Average Formula” builder (3 steps)
- Pick ignore zeros.
- Enter the range.
- Copy the formula without touching your data.
An average is easy to compute and easy to distort: zeros, blanks and weights
AVERAGE skips empty cells but counts zeros, and that difference moves the mean. If two months of data are missing and someone typed 0 for "no data", the average drops without looking odd. With AVERAGEIF and the criterion "not equal to zero" those values are excluded. The weighted average, for its part, divides the sum of products by the sum of weights, not by the number of items.
The tool assembles the four versions with your ranges and adds rounding by wrapping the formula in ROUND. That way you do not rely on a cell format that hides decimals yet keeps the full value, and that comes out different when you copy it to another sheet.
Which average fits which case
- •Complete data of equal importance: AVERAGE over the range.
- •Zeros that really mean "no data": AVERAGEIF with "<>0".
- •Each value weighs differently, like grades with credits: SUMPRODUCT divided by the SUM of the weights.
Three means from the same data
| Method | Formula | With 80, 90 and 0 |
|---|---|---|
| Plain | =AVERAGE(B2:B4) | 56.7 |
| Ignoring zeros | =AVERAGEIF(B2:B4,"<>0") | 85 |
| Weighted with 1, 1 and 2 | =SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4) | 42.5 |
Common Mistakes and How to Avoid Them
- ⚠️Averaging percentages without weighting by group size.
- ⚠️Including the total cell in the range, which pulls the mean off.
Frequently Asked Questions
Does AVERAGE count zeros?
Yes. It only skips empty cells and text. If a zero means there is no data, use the ignore-zeros option.
How do I make a weighted average?
Pick "Weighted average" and give the range of values and the range of weights. The tool divides the sum of products by the sum of weights.
Why round inside the formula and not with the format?
With ROUND the value really changes. With the format only what you see changes, and later calculations still use every decimal.
What I would do if I were in your shoes
«Before presenting a mean, look at the maximum and minimum: if they are far apart, the mean alone tells an incomplete story.» — DataKorex Engineering Team.