Weighted average

An average in which each value counts in proportion to its weight, such as units, revenue or headcount.

DefinitionAn average in which each value counts in proportion to its weight, such as units, revenue or headcount.

A weighted average gives each value an influence proportional to how much it matters, instead of counting every value once. Average selling price, average cost per unit and blended interest rates are all weighted averages, because a sale of 300 units should count for more than a sale of 100.

How it is computed

Weighted average = sum of (value × weight) / sum of weights. In Excel, that is SUMPRODUCT of the values and weights divided by SUM of the weights.

Example

100 units sold at $10 and 300 units at $14. The weighted average price is (100 × $10 + 300 × $14) / 400 = $5,200 / 400 = $13.00. The simple average of the two prices, $12.00, ignores that most units sold at the higher price.

Where it goes wrong

Averaging averages: taking the simple mean of regional average prices when the regions sold very different volumes. Weights and values that come from different periods or filters also give a number that matches nothing. The step-by-step guide is weighted average in Excel.

Articles that use this term

Finance metrics and formulas

Gross profit margin: formula, worked example and what moves it

Gross profit margin is revenue minus cost of goods sold, divided by revenue. This page gives the formula, what US GAAP and the IRS put into cost of goods sold, a worked example on three product lines with the Excel formulas, the weighted-average identity that proves the total, and the three things that move the margin: price, cost and mix.

Sep 30, 20266 min read Board and management reporting · Law firms

Lock-up on five clients: the whole arithmetic on one page

The complete lock-up calculation on five clients of a professional firm, small enough to check by hand: worked value from time entries, billed from bills, received from receipts, unbilled WIP and outstanding debtors, average daily billing, WIP days, debtor days, lock-up days and lock-up cash, the split that says whether the delay is billing or collection, the practice norm, and the identity that WIP and debtors reconcile, so a reader can reproduce every figure and then run it on their own practice management exports.

Sep 17, 20262 min read Finance metrics and formulas · Distributors

Calculating inventory days (DIO): formula, Excel and a worked example

Inventory days, or days inventory outstanding (DIO), is average inventory divided by cost of goods sold, times the days in the period. This page gives the formula and the data it needs, works it for five categories at a distributor with the Excel formulas, proves the total as a COGS-weighted average, and places it in the cash conversion cycle.

Oct 1, 20266 min read Finance metrics and formulas

Contribution margin ratio: formula, contribution per unit and the contribution margin income statement

The contribution margin ratio is revenue minus variable costs, divided by revenue. This page gives the three formulas (amount, per unit and ratio), which costs count as variable, a worked example on four products with the Excel formula, the contribution margin income statement beside the traditional layout, break-even and target-profit revenue from the ratio, and why product mix moves the blended ratio.

Oct 1, 20266 min read Finance metrics and formulas

Cost of goods sold (COGS): formula, what it includes and a worked example

Cost of goods sold is the direct cost of the goods a company sold in the period. This page gives the COGS formula, what US GAAP and the IRS put in and leave out, a worked year for a distributor with the Excel formulas, how FIFO, weighted average and LIFO change the figure, and what service companies call cost of revenue.

Oct 1, 20266 min read Finance metrics and formulas · SaaS

Customer acquisition cost (CAC): formula, calculator and a worked example

Customer acquisition cost is the sales and marketing cost of winning a new customer. This page gives the formula and what belongs in the cost, a small spreadsheet calculator for CAC and payback, a worked example across four channels with the weighted-average check, and the difference between blended, paid and fully loaded CAC.

Oct 1, 20266 min read