The weighted average, and the mean from a frequency table

By the end of this lesson you can work out a weighted average with SUMPRODUCT, find the mean when your data arrives as a frequency table, and explain why "the average of the averages" is usually wrong.

Why it matters

Amara sells oranges at ₦200 and watermelons at ₦2,500. Is her "average price" the average of her price tags? Not if she sells far more oranges than watermelons. The same trap catches reports every day: averaging the pass rates of a small school and a huge school as if they were the same size, or averaging the averages of regions with very different populations. The fix is simple once you see it.

The idea in plain words

In an ordinary mean, every value counts once. In a weighted average, some values count more than others, because they stand for more things: more pieces sold, more pupils, more people.

Think of a see-saw again (Lesson 2). If more children sit on one side, the balance point moves towards them. Weights are like extra children on a value.

Each fruit's price, with the bar as wide as the number of pieces sold. Synthetic data, made-up prices.

Weighted average: multiply each value by its weight, add these up, then divide by the total of the weights. In a spreadsheet: =SUMPRODUCT(values, weights)/SUM(weights).

Frequency table: a table that lists each value once, with how many times it appears. Its mean is a weighted average, with the counts as the weights.

See it done

Data: amara-stall-s1-sales.csv (synthetic). A random sample of 60 of Amara's sales. Column C is quantity (pieces), column D is amount_ngn (money for that sale), rows 2 to 61. The made-up prices per piece are: Mango ₦500, Orange ₦200, Banana ₦150, Pineapple ₦1,500, Pawpaw ₦1,200, Watermelon ₦2,500.

Part A: the average price a customer actually paid per piece.

1. The average of the six price tags: =AVERAGE(500,200,150,1500,1200,2500) gives ₦1,008.33. But this treats one watermelon the same as one orange, however many of each were sold.
2. Total money and total pieces: =SUM(D2:D61) gives ₦126,700 and =SUM(C2:C61) gives 152 pieces.
3. The weighted average price per piece: =SUM(D2:D61)/SUM(C2:C61) gives ₦833.55. Lower than the average of the tags, because she sold 49 cheap oranges but only 22 watermelons.

Part B: the mean from a frequency table. Sometimes you receive only the counts, not the raw list.

1. Build the table. In H2 to H7 type the quantities 1 to 6. In I2 type =COUNTIF(C$2:C$61, H2) and copy it down. You get 21, 10, 14, 8, 5 and 2 sales.
2. The mean from the table: =SUMPRODUCT(H2:H7, I2:I7)/SUM(I2:I7) gives 2.53 pieces per sale. Check it against the raw list: =AVERAGE(C2:C61) gives 2.53. The same.

How SUMPRODUCT reads. SUMPRODUCT(H2:H7, I2:I7) multiplies the first cells together (H2 × I2), then the second (H3 × I3), and so on, and adds up all the answers: 1 × 21 + 2 × 10 + … It works the same way in Google Sheets and Excel.

Part C, Real world: averaging averages.

Data: wdi-2024.csv (real). Column D is income_group, column F is life_expectancy_years, rows 2 to 189.

1. The average life expectancy in each income group, with AVERAGEIF. AVERAGEIF averages the cells that match a label: you give it the column to test, the label to match, and the column to average. For example, =AVERAGEIF(D2:D189, "Low income", F2:F189) reads "look in column D for Low income, and average the matching life expectancies in column F". It gives 63.4 years. The others: Lower middle 68.1, Upper middle 74.1, High 80.4.
2. The plain average of those four averages is 71.5 years. But the average of all 188 economies, =AVERAGE(F2:F189), is 73.9. They differ.
3. Why? The groups are different sizes: 20, 45, 52 and 71 economies. Weight each group's average by its size and you get 73.9 years: exactly the average of all 188.

Averaging the four group averages gives too much weight to the small Low-income group. Source: World Bank, World Development Indicators, 2024 values, CC BY 4.0.

Your turn, with help

A made-up shop sells three sizes of bread. Small loaves cost ₦500 and 60 were sold. Medium cost ₦800 and 30 were sold. Large cost ₦1,500 and 10 were sold. What is the average price paid per loaf?

Hints: Put the prices in one column and the numbers sold in the next. Use =SUMPRODUCT(prices, numbers sold)/SUM(numbers sold).

Show the answer

(500 × 60 + 800 × 30 + 1,500 × 10) ÷ (60 + 30 + 10) = ₦69,000 ÷ 100 = ₦690 per loaf. The plain average of the three prices, ₦933, is too high, because most loaves sold were small and cheap.

Your turn, alone

1. Made-up numbers: two classes each have 30 pupils. One class's average mark is 64 and the other's is 70. What is the average of all 60 pupils? Why can you just average the two averages here?
2. A made-up district has a small school with 40 pupils and a pass rate of 90 per cent, and a large school with 400 pupils and a pass rate of 50 per cent. What is the pass rate of all the pupils together? Give a whole number of per cent.
3. Explain to Amara in one sentence why "₦1,008 a piece" is not a fair description of the average price she gets per piece.

Watch out

• Averaging averages. It is safe when every group is the same size (or the averages happen to be equal). Otherwise, weight each average by its group size, or go back to the raw data.
• Choosing the weights. The weights must match the question. "Average price per piece" is weighted by pieces; "average amount per sale" is weighted by sales (each sale counts once). Say which one you used.
• One row is not always one person. Each row of the World Bank file is one economy. An average across rows treats a tiny island and a huge country the same. To get an average per person you would need population weights.

Say it back

1. How do you work out a weighted average?
2. How do you find the mean from a frequency table?
3. When is it safe to average a set of averages?

Show the answers

1. Multiply each value by its weight, add these up, and divide by the total of the weights: =SUMPRODUCT(values, weights)/SUM(weights).
2. Use the values as the numbers and the counts as the weights: =SUMPRODUCT(values, counts)/SUM(counts).
3. When all the groups are the same size; otherwise, weight each average by its group size.

What is next

The mean, median, mode and weighted average are the main centres. Two more are useful in special jobs: the trimmed mean, which ignores the most extreme values, and the geometric mean, the right average for growth. Lesson 8 shows both.

Formulas in this lesson were checked on 2026-10-11 against the official Google Sheets and Microsoft Excel help pages (SUMPRODUCT, AVERAGEIF, COUNTIF), and every number was calculated by running the formulas in a spreadsheet program (LibreOffice Calc) and checked by a separate calculation. Menu steps are written for Google Sheets on the web and Excel for Microsoft 365.

---

Answers to Your turn, alone

Show the answers

1. (30 × 64 + 30 × 70) ÷ 60 = 67, the same as the plain average of 64 and 70, 67. Because both classes have the same number of pupils, each gets the same weight, so averaging the two averages gives the right answer.
2. (40 × 90 + 400 × 50) ÷ 440 = 236 ÷ 440 = 54 per cent. Not 70 per cent, the plain average of the two rates, because the large school has ten times as many pupils.
3. For example: "₦1,008 is the average of your six price tags, but you sell many more cheap oranges than expensive watermelons, so the average price customers actually pay per piece is ₦834."