Weighted average with SUMPRODUCT

How it works, step by step

1. 01SUMPRODUCT($B$2:$B$200,$C$2:$C$200) multiplies each Unit price by the Units sold on the same row: 12.50 times 400 is 5,000, and 30.00 times 50 is 1,500.
2. 02It then adds those products into one number, the total revenue, 6,500 in this two-product example.
3. 03SUM($C$2:$C$200) adds the weights, the total units sold, 450.
4. 046,500 divided by 450 gives 14.44, the average price per unit sold. A plain AVERAGE of the two prices gives 21.25 because it treats 50 units the same as 400.

The same logic in four tools

=SUMPRODUCT($B$2:$B$200,$C$2:$C$200)/SUM($C$2:$C$200)

=SUMPRODUCT($B$2:$B$200,$C$2:$C$200)/SUM($C$2:$C$200)

SELECT SUM(unit_price * units_sold)::numeric
       / NULLIF(SUM(units_sold), 0) AS weighted_avg_price
FROM products;

import pandas as pd

weighted_avg_price = (
    (products["unit_price"] * products["units_sold"]).sum()
    / products["units_sold"].sum()
)
# np.average(products["unit_price"], weights=products["units_sold"])
# gives the same result but returns nan if either column has a NaN.

Mistakes that break it

• !The two ranges in SUMPRODUCT must be the same size. $B$2:$B$200 paired with $C$2:$C$199 returns #VALUE!.
• !SUMPRODUCT treats text as 0. A price stored as text drops out of the numerator while its units still count in SUM, which pulls the average down.
• !If total units is 0, the formula returns #DIV/0!. In SQL, NULLIF(SUM(units_sold), 0) returns NULL instead of raising a division error.
• !In Postgres, dividing two integer sums truncates the decimals. Cast one side to numeric, as the SQL above does.

Common questions

How do I calculate a weighted average in Excel?

Multiply each value by its weight with SUMPRODUCT, then divide by the sum of the weights: =SUMPRODUCT(B2:B200,C2:C200)/SUM(C2:C200).

Why is my weighted average different from AVERAGE?

AVERAGE gives every row equal weight. A weighted average gives rows with more units more influence, so a cheap product that sells in high volume pulls the result toward its price.

How do I do a weighted average in pandas?

Use np.average(df["value"], weights=df["weight"]). If the columns can contain NaN, use (df["value"] * df["weight"]).sum() / df["weight"].sum() instead.