Excel AVERAGE: numbers, missing values and weights

Before entering AVERAGE, I ask what one observation represents: a day, order, person or summary of an entire team? The function cannot answer that question. It follows the supplied values even when the range combines observations with different meanings.

The Polish name is ŚREDNIA. All examples below use synthetic data and cell ranges in Excel Microsoft 365, 2024, 2021, 2019 and 2016. English formulas use commas here; the separator required in your workbook depends on regional settings.

Find the denominator

Put the header result in A1. Enter 10 in A2, 20 in A3, leave A4 genuinely empty, enter text pending in A5 and numeric 0 in A6. Place the calculation outside the source, for example in C2.

=AVERAGE(A2:A6)

There are three numbers: 10, 20 and 0. Their sum is 30, so the average is 30 divided by 3, or 10. The denominator is neither every cell nor every nonempty cell. A5 contains text and occupies a cell, but contributes no numeric observation.

Microsoft’s documentation distinguishes numbers from text, logical values and empty cells in references. This entry focuses on ranges. Avoid extending a rule about referenced cells to every possible argument entered directly in an expression without checking the relevant behavior.

Missing data and zero answer different questions

Replace the blank A4 with numeric 0. The sum remains 30, but the range now contains four numbers. Its average falls to 7.5. Excel has followed the calculation rule; the data author has changed a missing observation into an observed zero.

In a daily report, a blank might mean an export has not arrived, while zero might mean a correctly observed day without events. I keep those states distinct. Showing available-observation and missing-observation counts beside an average helps the reader assess whether periods are comparably complete.

A number stored as text in the range may also be ignored. Formatting alone should not replace a check of the underlying data type. If an imported average looks suspicious, inspect recognizable values and the import convention before manually changing the answer.

Apply a condition to numbers or another column

For the original range containing 10, 20 and 0, the following formula selects positive values only. The average of 10 and 20 is 15.

=AVERAGEIF(A2:A6,">0")

The condition ">0" also removes negative numbers. If you want every nonzero number, "<>0" asks a different question. For -10, 0 and 20, the positive-only average is 20, while the nonzero average is 5. I therefore avoid describing a positive filter as a universal way of ignoring zeros.

The full syntax is AVERAGEIF(range,criteria,[average_range]). In a separate worksheet, put statuses ready, ready, pending in A2:A4 and numbers 10, 20, 90 in B2:B4. Then use:

=AVERAGEIF(A2:A4,"ready",B2:B4)

The expected result is 15. The condition concerns statuses, while the averaged numbers come from the second column. Microsoft documents the optional third argument. If no values meet the condition, expect a division-by-zero error rather than a zero average.

Calculate a weighted mean

In another worksheet, put headers value and weight in A1 and B1. Enter 10, 20, 30 in A2:A4 and weights 1, 2, 3 in B2:B4. Here, weights are positive numbers defining the observations’ relative contributions.

=SUMPRODUCT(A2:A4,B2:B4)/SUM(B2:B4)

SUMPRODUCT multiplies corresponding entries and sums their products. Its Polish name is SUMA.ILOCZYNÓW; the old Polish text used an incorrect name. The numerator is 10 times 1, plus 20 times 2, plus 30 times 3: 140. The weights total 6, so the mean is approximately 23.33.

The value and weight ranges must align by position and have matching dimensions. Check completeness in both columns and a positive total weight. A missing value paired with a retained weight can change the meaning of the ratio. A function’s treatment of text is not a complete data-cleaning procedure.

Errors, filters and hidden rows

With no numeric values in the range, AVERAGE returns #DIV/0!, called #DZIEL/0! in Polish Excel. Source error values also require investigation. IFERROR can hide several different causes behind one message, so diagnose the source before deciding which display message is appropriate.

Ordinary AVERAGE uses its referenced range; hiding a row does not redefine that range. For a mean of visible rows, read about SUBTOTAL, including its distinction between filtered and manually hidden rows. That behavior needs an explicit reporting decision.

Check the measure’s meaning

Practice replacing the blank with zero and explain the denominator change. Then compare the ordinary mean of 10, 20, 30 with the weighted example above. The dataset is small enough to explain each contribution without relying on a large output table.

If the input values are already group summaries, do not automatically give them equal weight. A group of two observations and a group of two hundred may represent very different contributions to the question. Define the unit of analysis first. For further conditional aggregation, continue with SUMIFS.