Calculate weighted prices and gross margins from sales rows in an Excel pivot table. Sum each measure's numerator and denominator separately, then divide and reconcile the grand total.
A weighted average pivot table divides a summed numerator by its summed weight: revenue by units for selling price, or gross profit by revenue for margin. Add the components to the pivot as sums, then calculate their ratio beside the report. Repeat that division at the grand total instead of averaging the customer results.
The correct weight depends on the question. An order price describes a unit, so units determine its influence. A gross margin percentage describes a revenue dollar, so revenue determines its influence.
Weighted selling price = Sum of net revenue / Sum of net units
Weighted gross margin = Sum of gross profit / Sum of net revenue
These are both weighted averages, but their denominators differ. The existing weighted average in Excel guide covers SUMPRODUCT directly. Here the job is to preserve those components through pivot grouping, filters and grand totals.
Use net revenue after discounts and credits. For the example below, every row is the same filter cartridge, sold by a US industrial distributor during one completed month. Keeping the product consistent makes the customer price comparison meaningful.
Put the following headers in A1:G1. Paste the four sales lines into rows 2 through 5. Revenue and cost must cover the same shipped units and reporting period.
| Customer | Product | Units | Unit price (USD) | Net revenue (USD) | COGS (USD) | Gross profit (USD) |
|---|---|---|---|---|---|---|
| Atlas | Filter cartridge | 10 | 100 | 1,000 | 700 | 300 |
| Atlas | Filter cartridge | 90 | 80 | 7,200 | 5,400 | 1,800 |
| Beacon | Filter cartridge | 20 | 90 | 1,800 | 1,260 | 540 |
| Beacon | Filter cartridge | 30 | 100 | 3,000 | 1,950 | 1,050 |
| Total | 150 | 13,000 | 9,310 | 3,690 |
For this example, E2 and G2 contain:
=C2*D2
=E2-F2
Fill both formulas down through row 5. In a real export, use the posted net revenue if discounts or credits mean units multiplied by the displayed list price would differ. The numerator needs actual realized revenue.
Select A1:G5 and turn it into an Excel table through Insert > Table, confirming the header row. Do not include the illustrative total row in the source table: a pivot must aggregate transaction rows once. Give the table a recognizable name, such as SalesRows.
Microsoft's pivot table instructions recommend consistent column types and explain that new table rows enter the pivot source when it is refreshed. Check that units, revenue and costs are numeric before creating the report.
Select a cell in the source table, choose Insert > PivotTable, and place it on a new worksheet. Put Customer in Rows. Add Units, Net revenue (USD), COGS (USD) and Gross profit (USD) to Values, setting every field to Sum through Value Field Settings.
Do not add Unit price as Average and label it weighted price. Microsoft's pivot calculation documentation distinguishes Sum from Average: Average summarizes source values without applying the business weights you need.
For a reproducible worksheet layout, place the pivot at A3. With one row field and four summed values, arrange the output columns as follows; Atlas is row 4, Beacon row 5 and Grand Total row 6.
| Customer | B: Sum of Units | C: Sum of Net revenue (USD) | D: Sum of COGS (USD) | E: Sum of Gross profit (USD) | F: Weighted price (USD) | G: Weighted margin |
|---|---|---|---|---|---|---|
| Atlas | 100 | 8,200 | 6,100 | 2,100 | 82.00 | 25.61% |
| Beacon | 50 | 4,800 | 3,210 | 1,590 | 96.00 | 33.13% |
| Grand Total | 150 | 13,000 | 9,310 | 3,690 | 86.67 | 28.38% |
F and G are ordinary worksheet columns beside the pivot. In F4 and G4 enter these formulas, respectively:
=IF(B4=0,"",C4/B4)
=IF(C4=0,"",E4/C4)
Fill down through row 6, including Grand Total. Format F to two decimal places and G as a percentage with two decimal places. Retain full precision underneath. Atlas therefore has $8,200 / 100 = $82.00 per unit and $2,100 / $8,200 = 25.61% margin.
If your pivot has extra headers or a different starting cell, adjust the references to its actual rows. Leave room beside the report and recheck formula coverage after adding customers. A worksheet formula does not automatically become part of the pivot's expansion rules. The broader Excel pivot table guide covers the initial report setup in more detail.
The price check is $13,000 / 150 = $86.67 per unit, rounded. The unrounded price multiplied by 150 must rebuild the $13,000 of revenue. The margin check is $3,690 / $13,000 = 28.38%, rounded; the unrounded margin multiplied by revenue must rebuild $3,690 of gross profit.
Use these source checks outside the pivot:
=SUM(SalesRows[Net revenue (USD)])/SUM(SalesRows[Units])
=SUM(SalesRows[Gross profit (USD)])/SUM(SalesRows[Net revenue (USD)])
=SUM(SalesRows[Net revenue (USD)])-SUM(SalesRows[COGS (USD)])-SUM(SalesRows[Gross profit (USD)])
The first two match the unfiltered grand total ratios; the third returns zero. Filtered reports need checks against the same selected rows, rather than against the entire source table.
The simple average of the four source prices is (100+80+90+100)/4, or $92.50. Averaging the two customer weighted prices gives (82+96)/2, or $89.00. Neither equals $86.67 because both discard the relevant quantities. Atlas has more units, so its lower price has more influence on the combined result.
For margin, averaging the four row percentages gives (30%+25%+30%+35%)/4 = 30.00%, also different from the correct 28.38%. The numerator and denominator totals provide the proof; the displayed averages cannot replace them.
Covirage is not affiliated with Microsoft.
Bring your sales export with customer, product, units, net revenue and matching costs. Covirage maps the columns, and its tools compute and check the weighted prices and margins; the external AI model explains the results with every figure traced to its rows. Create a free Covirage account, which takes about a minute, then set up your business and bring a sample of your own data.
See Excel analysis for the supporting use case.
Average gives each source value equal weight. A small order therefore counts as much as a large order. A weighted business measure instead divides the summed numerator by the summed relevant weight, such as revenue divided by units for average selling price.
Use net revenue as the weight. Each row's gross margin multiplied by its net revenue equals gross profit. Dividing total gross profit by total net revenue gives the business margin. Units are appropriate for average unit price, but they do not weight gross margin correctly.
Only when customer weights happen to be equal, or the values happen to produce the same result. Usually they differ. Recalculate the grand total from the total numerator and total denominator so the result describes all included sales rows.
Leave the ratio blank and flag the group for review. Zero net units can arise from offsetting sales and returns; zero net revenue can also make margin undefined. Do not substitute zero percent or silently remove the underlying dollars from the reconciliation.