Calculate blended ROAS without averaging campaign ratios
FormulaCraft · Reviewed 2026-09-16
For a combined campaign report, weight each campaign's ROAS by its spend. A campaign spending 100 at 10× ROAS and another spending 900 at 2× ROAS produce a blended 2.8×, not the unweighted average of 6×. Use comparable attributed revenue, currency and reporting windows across all campaigns.
Spend-weighted ROAS
| Campaign | Spend | ROAS |
|---|---|---|
| Launch | 100 | 10 |
| Retargeting | 900 | 2 |
Excel
=SUMPRODUCT(B2:B3,C2:C3)/SUM(B2:B3)Google Sheets
=SUMPRODUCT(B2:B3,C2:C3)/SUM(B2:B3)Expected result: 2.8
Campaign revenues implied by the ratios are 100 × 10 = 1000 and 900 × 2 = 1800. Combined revenue 2800 divided by combined spend 1000 equals 2.8.
Both expressions are checked against the expected result in HyperFormula. This is an engine check, not a native Excel or Google Sheets certification. The CSV contains sample data; paste the formula into an empty cell.
Keep each campaign's spend beside its ratio
In the sample, column B contains spend and column C contains ROAS expressed as a numeric multiple. Enter 2 for 2×, not a string such as 2x. Keep both campaigns in the same reporting currency and period.
The sample deliberately gives the campaigns very different spend levels. This makes it easy to see why giving each campaign equal weight would distort the combined result.
Check the arithmetic before using the formula
The first campaign implies 1,000 in attributed revenue and the second implies 1,800. Total spend is 1,000 and total implied revenue is 2,800, so the correct combined ratio is 2.8×.
SUMPRODUCT multiplies each spend by the ratio beside it and adds the products. Dividing by total spend gives the blended ratio. Both input ranges must cover corresponding campaign rows.
Prefer original revenue amounts when available
If the platform provides spend and attributed revenue, aggregate those original amounts directly. Reconstructing revenue from a rounded ROAS value can introduce rounding differences.
A blended platform report may double-count conversions if different platforms attribute the same order to themselves. Weighting the ratios correctly does not resolve attribution overlap. State whether the report uses platform-attributed or independently reconciled revenue.
Handle missing values and zero spend explicitly
The example assumes numeric spend and ratios with positive total spend. If total spend is zero, the ratio is undefined. Report that condition as unavailable instead of inventing a zero or hiding it behind a generic error handler.
Investigate missing ratios before calculating the result. A blank may represent missing tracking, no revenue or an incomplete export; those situations require different reporting decisions.
Common questions
Why not average the two ROAS values?
That gives a campaign spending 100 the same influence as one spending 900. Spend weighting reflects their contributions to the combined ratio.
Is 2.8× the same as 280%?
As a mathematical ratio, yes. This example stores ROAS as a multiple. Be consistent when choosing numeric values and display formatting.
Does blended ROAS equal profit?
No. The example relates attributed revenue to advertising spend. It does not deduct product costs, fulfilment, returns or other expenses.