Don’t use =AVERAGE() on a column of margin percentages to get your overall margin. A simple average gives a $200 add-on the same weight as an $8,000 order. Gross margin is gross profit divided by revenue, so the overall figure has to be weighted by revenue. If your sheet has gross profit dollars, divide total profit by total revenue: =SUM(D2:D5)/SUM(B2:B5). If you only have revenue and margin rates, SUMPRODUCT works under the conditions below.
In the example below, AVERAGE reports 55.00%, which is 29.10 percentage points above the real 25.90%.
Four Orders, $10,000 in Revenue
This is an example for educational purposes. Put it on a sheet named Margins with headers in A1:F1, orders in rows 2 to 5, totals in row 6 and the wrong average in row 7.
Type the revenue and COGS amounts. Enter =B2-C2 in D2, =D2/B2 in E2 and =B2/$B$6 in F2, then fill down to row 5. Row 6 holds =SUM(B2:B5), =SUM(C2:C5), =SUM(D2:D5) and =SUM(F2:F5) under their columns, with =D6/B6 in E6. E7 holds =AVERAGE(E2:E5).
Test each margin against your profit. =B6*E6 returns $2,590.00, matching D6. =B6*E7 returns $5,500.00, or $2,910 of gross profit nobody earned. A margin that can’t rebuild your profit total is wrong.
Why the Simple Average Goes Wrong
The Core Hardware order brings in 80% of revenue, and the add-on brings in 2%. AVERAGE gives each row 25% of the weight, so the add-on counts 12.5 times its share of sales and the large order shrinks from 80% to 25%. Because the small orders carry the highest margins, the average drifts up toward them.
The rule: weight a percentage by its own denominator. Weight gross margin by revenue, conversion rate (orders / visits) by visits, and average unit price by units sold, which What Does One Row Represent in Your Sales Spreadsheet? covers.
A simple average of margins can describe the typical listed margin across a catalog, ignoring sales volume. It doesn’t reliably measure the margin the business earned.
How to Calculate Revenue-Weighted Margin
Default: total profit divided by total revenue
This works in both apps, avoids rounding drift and handles rows with costs but no revenue. Replace E6 with a guarded version:
=IF(AND(COUNTA(A2:A5)=4, COUNT(B2:B5)=4, COUNT(D2:D5)=4, SUM(B2:B5)>0), SUM(D2:D5)/SUM(B2:B5), "CHECK INPUTS")
It returns 25.90%. COUNTA confirms four order rows, and the COUNT tests confirm every revenue and profit cell is a number. SUM skips text, so a profit amount pasted as text would lower the margin with no error, and a positive revenue total wouldn’t catch it. The last test blocks dividing by zero or negative revenue.
On a longer table, change the ranges and counts, and separately confirm every order made it in by matching the row count and revenue total to your sales report.
Alternative: SUMPRODUCT when you only have rates
If you have revenue and margin percentages but no profit dollars, SUMPRODUCT multiplies each revenue by its margin and adds the results. Enter a check in an empty cell such as H2:
=AND(COUNTA(A2:A5)=4, COUNT(B2:B5)=4, COUNT(E2:E5)=4, COUNTIF(B2:B5,">0")=4)
In H3:
=SUMPRODUCT(B2:B5, E2:E5)/SUM(B2:B5)
H3 returns 25.90%. Trust it only when H2 says TRUE and:
- Every revenue is positive. COUNTIF tests this.
- The rates are unrounded. Margins copied from a report rounded to whole percentages rebuild only approximate profit.
- Every cell is a number. Microsoft’s SUMPRODUCT documentation says Excel treats nonnumeric entries as zeros. If E2 holds the text “20%”, Excel drops that row’s $1,600 and reports $990 / $10,000 = 9.90%, with no error. H2 returns FALSE because COUNT(E2:E5) falls to 3. In Google Sheets, SUMPRODUCT accepts that text “20%” and still returns 25.90%, while AVERAGE.WEIGHTED returns #VALUE!. In either app, rely on the check, not on an error.
In Google Sheets, =AVERAGE.WEIGHTED(E2:E5, B2:B5) also returns 25.90%, values first and weights second. Excel shows #NAME?. Google’s documentation says weights can’t be negative and at least one must be positive. The same three conditions apply.
With positive revenue, complete numeric inputs and unrounded rates, all three methods give the same result.
Pivot Tables Make the Same Mistake
Build the pivot from A1:F5 only, so the total and average rows aren’t counted as orders. With Gross_Margin_Pct in Values, AVERAGE gives a grand total of 55.00% and SUM gives 220.00%. Sum the dollars first:
- Either app: Add Revenue and Gross_Profit to Values as SUM, then divide the grand-total profit by the grand-total revenue in a cell beside the pivot.
- Google Sheets: Choose Values > Add > Calculated field, enter
=SUM(Gross_Profit)/SUM(Revenue)and set Summarize by to Custom (Google’s instructions).
To check the Google Sheets calculated field, put Order_ID_Line in Rows. Each group shows its own profit over its own revenue: 20.00%, 40.00%, 70.00% and 90.00%. Only the Grand Total shows 25.90%.
Troubleshooting
| Symptom | Cause | Fix |
|---|---|---|
| Total revenue × margin ($5,500) doesn’t match total profit ($2,590) | The margin uses =AVERAGE(E2:E5) |
Use =SUM(D2:D5)/SUM(B2:B5) |
| Excel SUMPRODUCT returns 9.90% with no error | E2 contains the text “20%”, which Excel counts as zero | Run the H2 check; convert text to numbers |
| E6 shows CHECK INPUTS | A blank or text revenue or profit cell, or total revenue isn’t positive | Fix cells until each COUNT returns 4 |
| SUMPRODUCT returns #VALUE! | Ranges differ in size, or a cell holds an error such as #DIV/0! | Match the ranges; use the default ratio for zero-revenue rows |
| Pivot shows 55.00% or 220.00% | It averages or sums Gross_Margin_Pct | Sum the dollars first |
Frequently Asked Questions
When does a simple average equal the weighted margin?
Two common cases: every row has the same revenue (say, $2,500 on each order, giving 55.00% both ways), or every row has the same margin. They can also match by coincidence. Orders of $100, $200 and $300 at 10%, 30% and 10% earn $100 on $600, or 16.67%, and the simple average is also 16.67%. A match doesn’t make AVERAGE the right method.
Can I average 12 monthly margins to get the annual margin?
No. If December brings $200,000 at 30% and February brings $40,000 at 50%, the simple average of those months is 40%, but the combined margin is $80,000 / $240,000 = 33.33%. Divide total annual gross profit by total annual revenue.
What if a row has zero or negative revenue?
Take a free warranty replacement: $0 revenue, $50 COGS, −$50 gross profit. On a copy, insert a row above row 6, moving totals to row 7 and the average to row 8. Enter a label, 0, 50 and =B6-C6 in A6:D6, leaving E6 empty because a margin on zero revenue is undefined. Point the row 7 totals at rows 2 to 6, with counts of 5 in the guarded formula. E7 shows $2,540 / $10,000 = 25.40%. SUMPRODUCT over B2:B6 and E2:E6 multiplies $0 by an empty or 0% margin and still reports 25.90%, losing the cost.
For returns and credits, AVERAGE.WEIGHTED rejects negative weights. Divide net gross profit by net revenue while net revenue is positive; otherwise the guarded formula says CHECK INPUTS.
Next Step
Write the definition where everyone using the sheet can see it: gross margin equals total gross profit divided by total revenue. How to build a monthly KPI scorecard covers keeping metric definitions consistent.