Overview
When combining group averages, weight each group by the quantity represented by its average. For an overall rate, add the underlying numerators and denominators when the definitions are compatible, then divide. Averaging percentages or averages directly gives every group equal influence, even when their sizes differ.
The right calculation depends on the question. An average branch result and an average customer result are different measures. Neither label should be used for the other.
Microsoft's average and SUMPRODUCT documentation shows the spreadsheet mechanics. The important business decision comes first: what should one unit of weight represent?
Start with the underlying unit
Ask what is being averaged: orders, customers, hours, units sold, or branches. That unit determines the denominator.
For average order value, the denominator is the number of qualifying orders under the chosen definition. For average price per unit, it is the number of units. A customer who places several orders therefore has different influence in those two calculations.
Write the definition in the metric change log. A formula can be mathematically correct while answering a question the dashboard label does not describe.
Keep the period and eligibility rules consistent across groups before combining them.
Work through unequal group sizes
Suppose one branch handles 10 orders with an average value of 100. Another handles 90 orders with an average value of 20.
The simple average of the two branch averages is 60. That answers the question “What is the average of these two branch-level averages?” It does not describe the average order across both branches.
The first branch represents 1,000 in order value and the second 1,800. Together, that is 2,800 across 100 orders, so the combined average order value is 28.
The weighted calculation is:
Combined average = sum of each group average × its order count ÷ total order count.
The larger branch receives more weight because it contributes more orders, not because its performance is considered more important.
Rebuild rates from counts
Suppose a small campaign produces 8 responses from 10 eligible contacts, while a larger campaign produces 20 from 100. Their rates are 80% and 20%.
A simple average gives 50%. The combined response rate is 28 responses divided by 110 contacts, or about 25.5%.
This works only if “response” and “eligible contact” mean the same thing in both groups and the contacts are appropriately counted. If the campaigns overlap, adding denominators may count the same person twice.
Use counts where available rather than rounded displayed percentages. Reconstructing totals from a rate rounded to a whole percentage can introduce avoidable error.
Choose weights that match the question
Order counts are suitable for combining average order values. Hours are suitable for combining rates expressed per hour when the numerator and exposure are consistent. Revenue weights may be appropriate for some margin calculations, depending on the exact definition.
Do not choose weights merely because a column is available. Headcount does not necessarily weight a productivity rate correctly if employees worked different hours.
There are also legitimate equal-weight questions. A manager may want each branch to have equal influence when describing the typical branch-level result. Label that choice clearly and show the distribution where it matters.
Weighting is a definition decision before it is a spreadsheet operation.
Use the spreadsheet formula carefully
If group averages are in B2:B6 and the corresponding counts are in C2:C6, a common calculation is:
=SUMPRODUCT(B2:B6,C2:C6)/SUM(C2:C6)
The ranges must align row by row. Microsoft documents how SUMPRODUCT multiplies corresponding entries and sums the results.
Check that the denominator is positive and that all included values represent compatible quantities. A zero total weight leaves the weighted average undefined; it should not be replaced with a misleading zero result.
If raw totals are available, dividing their sum by the sum of the relevant counts can be clearer than reconstructing totals from averages.
Treat missing values deliberately
A missing average with a positive count is not the same as a zero average. If the formula silently treats the missing value as zero while retaining its weight, the result can be biased downward.
Use the missing-data guide to define whether the calculation excludes incomplete groups, pauses, or reports a partial result. Make the covered population visible.
Do not remove a difficult group without explaining the exclusion. A combined average based only on reporting branches may not represent the whole business.
Check imported text, blanks, and errors before trusting the spreadsheet result.
Keep group differences visible
A weighted overall result can change because group performance changed or because the mix of activity changed. The segment-mix guide explains why those effects can move in different directions.
Show the component counts and rates when the distinction matters. An overall improvement may come from more activity in an already strong segment while weaker segments remain unchanged.
Likewise, a falling average does not automatically mean every team performed worse. The mix may have shifted toward smaller orders or a different type of customer.
The overall measure and the component view answer complementary questions.
Check the result before publishing
For nonnegative weights, a weighted average should lie between the smallest and largest included values. A result outside that range suggests a formula, data, or definition problem.
Recalculate a small example manually and compare it with the spreadsheet. Confirm that the total weight matches the population described in the report.
Label the unit, period, and weighting basis. “Average: 28” is incomplete; “Average value per qualifying order, all branches, August” is interpretable.
A reliable combined average preserves the relationship between the result and the amount of activity behind it. The denominator is part of the meaning, not just the bottom half of a formula.
References and examples
Primary sources and product examples used to ground this guide. Product links are editorial references, not endorsements.