Basic Functions
Advanced

A weighted average with SUMPRODUCT

Each value counts in proportion to its weight.

Task:

Stock was bought in batches at different prices. In B9 give the average cost per unit across all the stock — each batch's price weighted by how many units it contained.

Hints

+5 XPno-hints bonus

Solve it on your own to keep the bonus. Each hint gets one step closer to the formula.

Variations of SUMPRODUCT

0/3

The same formula in the other shapes it takes at work.

  1. SUMPRODUCT Function

    Calculate weighted sums using SUMPRODUCT.

    Core
    Advanced
  2. SUMPRODUCT for an order total

    Quantity times price for every line, added up, in one cell.

    Intermediate
  3. A weighted average with SUMPRODUCT

    Each value counts in proportion to its weight.

    Advanced

The data in this exercise

This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.

9 rows × 3 columns1 cell you fill in
ABC
1BatchUnitsPrice per unit
2B15002.4
3B22002.9
4B310002.1
5B43003.2
6
7
8
9Average unit cost
What this exercise teachesMay contain the answer

AVERAGE(C2:C5) treats the 200-unit batch and the 1,000-unit batch as equally important and gives 2.65. Weighting by units gives about 2.42 — the real cost of a unit taken off the shelf, and the figure to use for margins.