AVERAGEIF in Excel: average readings for one category

Build a category and reading pair

A workshop checks vibration readings from two equipment groups, Bench and Floor. The log has five rows: Bench 4, Floor 10, Bench 8, Floor 14 and Bench with no reading yet. This is a teaching dataset, not a safety limit or maintenance recommendation.

Put the groups in A2:A6 and their readings in B2:B6. Put Bench in D2 as the category to report. The two columns must describe the same row of the log: the reading in B4 belongs to the group in A4.

Keep the condition range separate from the numbers

The first range contains the category labels. D2 supplies the label to match. The final range contains the numbers to average. With Bench selected, the numeric readings are 4 and 8, so the result is 6.

A6 also says Bench, but its reading is missing. It does not become a third numeric observation. This is an average of the available Bench readings, not an average over every Bench row.

=AVERAGEIF(A2:A6,D2,B2:B6)

Verify the other category by hand

Change D2 to Floor. The included readings are now 10 and 14, giving 24 divided by 2, or 12. Change D2 back to Bench to restore 6.

This small manual check tests the direction of the ranges. If you accidentally use the reading column as the condition range, the text Bench will not select the intended rows. A plausible-looking average alone is not proof that the correct group was selected.

A zero is a reading; a blank is missing

With Bench selected, enter 0 in B6. The average becomes (4 + 8 + 0) divided by 3, or 4. Enter 6 instead and the average becomes 6. Clear B6 and it returns to the original two-reading average of 6.

The last two results happen to match even though they use different numbers of observations. Check the underlying rows as well as the average. A group label does not prove that its reading has been entered.

No matching group is not a zero average

Change D2 to Ceiling, a group absent from this dataset. The result is #DIV/0!, because there are no numeric readings to average for that selection. A zero average would be a different claim: it would say that included numbers averaged to zero.

Before adding error handling, check the selected category and the source labels. Extra spaces can prevent the intended match. Avoid turning every error into zero: that can conceal an incorrect range or a data problem.

Use explicit, aligned ranges

Keep A2:A6 and B2:B6 aligned in this example. If you add a sixth observation in row 7, extend both ranges to include it. Copying a formula beside several category labels is another reason to lock the source ranges while allowing the category reference to move.

The locked version below still uses D2 as the selector. If you copy it down one row, the intended selector becomes D3 while the source rows stay fixed. In the practice panel, editing the formula does not itself perform a copy-down operation.

=AVERAGEIF($A$2:$A$6,D2,$B$2:$B$6)

Know when one condition is not enough

This report asks for one category. If you also need a date period or a completed-inspection flag, the task has multiple conditions; use AVERAGEIFS and check which rows satisfy all of them. Do not average separately calculated category averages unless their observation counts justify that weighting.

For this lesson, all entered readings are ordinary numbers. Validate imported text, logical values and error cells before treating this pattern as a general-purpose data-cleaning formula.

Further reading

• Microsoft: AVERAGEIF function
• Microsoft: diagnosing division-by-zero errors