Weighted Average Price with SUMPRODUCT
Your task: In E2:E4, find total revenue, the weighted average price, and the gap to the simple average.
Full instructions, scenario and goal
Scenario
A pricing analyst studies six products from one category. Some sell thousands of units cheaply and others a few units at a premium. The analyst wants the revenue of the whole category, the average price actually paid per unit, and a measure of how misleading the simple average of the list prices is.
Your task
- In E2, calculate the total revenue: units multiplied by price, added up over all products.
- In E3, calculate the average price paid per unit: total revenue divided by total units.
- In E4, calculate the simple average of the six prices, then subtract the weighted average to show the gap (simple minus weighted).
- Use the table in rows 2 to 7, then select Check Answer.
You are done when
- Revenue is calculated without a helper column.
- The weighted average divides revenue by the total number of units.
- The gap compares the plain average of prices with the weighted one.
What you will practise
Multiply two columns and add the results in one formula with SUMPRODUCT, and use it to compute a weighted average.
The idea in 30 seconds
SUMPRODUCT multiplies the matching cells of two or more ranges and adds up all the products. With a column of units and a column of prices, SUMPRODUCT(units, prices) is the total revenue, with no helper column of line totals. The ranges must have the same size.
Spreadsheet
Loading spreadsheet…
Keyboard shortcuts
- Arrow keys
- Move between cells
- Shift + Arrow keys, or drag with the mouse
- Select a range
- Ctrl + Arrow keys
- Jump to the edge of a block of data
- Type, or F2, or double-click
- Edit a cell (F2 switches arrow keys between moving and editing)
- Click or arrow keys inside a formula
- Insert a cell reference after =, +, (, comma and similar
- F4
- Cycle a reference through A1, $A$1, A$1, $A1
- Enter / Tab while editing
- Confirm and move down / right (Shift reverses)
- Esc
- Cancel the edit
- Ctrl + C / X / V
- Copy, cut and paste (formulas adjust like in Excel)
- Delete
- Clear the selected cells
- Ctrl + Z / Ctrl + Y
- Undo and redo
- Ctrl + Enter
- Check your answer
- Tab when not editing
- Leave the spreadsheet
Type your answer in cell E2. Click a cell and type. Enter confirms, Esc cancels, Ctrl+Enter checks your answer.Tap a highlighted cell to type in it, then tap the green tick to confirm. Scroll the sheet sideways if it is wider than your screen.
New here? How an exercise works
- Read the task. Highlighted cells are yours to fill in; the rest are locked.
- Select (or tap) a highlighted cell and type a formula. It starts with =, like =A1+B1.
- Press Check answer. Stuck? Reveal hints one at a time.
The full explanation: the idea behind this exercise
SUMPRODUCT multiplies the matching cells of two or more ranges and adds up all the products. With a column of units and a column of prices, SUMPRODUCT(units, prices) is the total revenue, with no helper column of line totals. The ranges must have the same size.
A plain AVERAGE of prices treats a product that sold 40 units like one that sold 2,500. A weighted average counts each price as many times as it sold: total revenue divided by total units, that is, SUMPRODUCT of the two columns divided by the SUM of the weights. When the volumes are uneven, the weighted average is the price customers really paid on average, and it can differ a lot from the simple one.
Skills you will practise
- SUMPRODUCT
- Weighted average
- AVERAGE
- Business analysis
Complete first: Profit and Margin by Product
Common mistakes to avoid
- Using AVERAGE for a weighted average
- AVERAGE gives each price the same importance, however many units were sold.
- Ranges of different sizes
- SUMPRODUCT needs equally sized ranges, so rows 2 to 7 in both columns.