Averaging two group averages gives each group equal influence, regardless of how many observations it represents. That may be appropriate when the groups themselves are the units of interest. If the objective is the average across all observations, the group sizes matter.

Confirm that the averages measure the same quantity and cover compatible observations before combining them. Keep each group’s average beside its contributing count. A count of ten records is not interchangeable with a count of ten groups.

In Numbers, SUMPRODUCT multiplies corresponding values across collections and adds the products. The collections must have the same dimensions. With one collection, it returns that collection’s sum. These behaviours allow the weighted numerator and total weight to remain explicit.

Compare unequal groups with simple arithmetic

In a practice table, put group averages 10 and 20 in A2:A3 and corresponding observation counts 2 and 8 in B2:B3. The first group contributes 10 multiplied by 2, or 20; the second contributes 20 multiplied by 8, or 160. The combined total is 180 across 10 observations, giving 18.

Use =SUMPRODUCT(A2:A3,B2:B3)/SUMPRODUCT(B2:B3) for the example. The denominator uses SUMPRODUCT with one range to add the weights. The expected result is 18. Simply averaging the two displayed group means gives 15, which answers an equal-group question instead.

Check the numerator and denominator independently before accepting the final result. If a weight has been paired with the wrong average, the formula can calculate successfully while describing the wrong population. Keep the corresponding rows aligned during every sort or edit.

Define which observations qualify

Use nonnegative counts for this count-weighted example and decide how missing group averages will be handled. Do not assign a missing mean the value zero merely to obtain an answer. If the total eligible weight is zero, there is no weighted mean to report; identify that condition rather than presenting a made-up zero result.

For a real report, retain sufficient precision in the group averages or, preferably when available, use the underlying totals and counts. Previously rounded means may limit the precision that can be recovered from the summary.

Label the output with what was weighted and which groups were included. Reconcile a small subset against its original observations and repeat the check when a group is added. An accepted weighted average should be understandable from its values, weights and inclusion rule, rather than only from an error-free formula.

Reference: Apple: SUMPRODUCT.