Using Averages

I’m going to try something new here.

A couple of weeks ago, someone was questioning my calculations of averages and it got me thinking: why use weighted average rather than average? I think most people understand the use of averages but I am not so sure they understand weighted averages. The concept of weighted average makes intuitive sense to me but then, I do have an intuitive feel for numbers. The question about the calculations and the heavy use of averages in the spreadsheets I see makes me wonder if the concept of weighted average is understood by others outside of science and engineering.

Fortunately, my boss understands the concept.

So here, I decided to try to think of the concept in picture terms.

The way I see it, weighted average utilizes the concept of weights. The calculations below show how the weighted averages are calculated. We’ll see if I can explain it using pictures.

Pretend you are a boss with 3 teams responsible for filling up their 10-seater plane. Your first team has managed to sell 3 seats or 30% occupancy. Your second team, team B, sold 8 seats or 80%. Team C reached 60% occupancy. The average occupancy in your division comes out to be 56.67%, as shown in the calculations in the image to the left. In this instance, both average and weighted average comes out to be the same. That’s because each team has 10 seats so each team is equally weighted. The weights are the number of seats and each team contributes equally to the overall average occupancy %. The weights are applied to each team’s occupancy % to arrive at your overall occupancy %.

 

 

 

 

Now let’s change it up slightly by pretending team A has a 100 seater plane, a really large plane, and the other two teams still have 10 seats each . So your division now has 120 seats. Here, team A managed to fill up 30 seats or 30% occupancy. Their occupancy percentage is the same as before but their weight in your overall occupancy percentage becomes much greater. Team A contributes 100 seats to your total 120 seats (rather than 10 out of 30 in the prior example). Using the same set of calculations as we used in the prior example, we find that the average calculations remain the same at 56.67% whereas the weighted average gives you 36.67%. Each team still has the same occupancy % as before but for you, the results are different. Which should you use? My intuition says weighted average because team A is contributing 100 seats out of your 120 total.

The next picture illustrates why (at least, I think it does).

 

Instead of thinking of 3 separate teams, put it all into one team, where the first row of dots in the picture represents 30 filled seats out of 100 and the next 2 rows contain 10 seats each. Pretend this picture is of one big team with 30+8+6=44 seats filled out of 120 seats. Now your average calculates to 36.67%, just like in the prior weighted average calculation.

So if you have teams with extreme diversity of contributions or results, the type of average calculation can become critical. When you have different players or teams providing different levels of contributions, you should use the weighted average or you could be really off.

 

 

When using Excel, using SUMPRODUCT is a really good function to use to calculate weighted averages. Its syntax is: SUMPRODUCT(range1, range2) where range 1 is one series of numbers (R1A, R1B, R1C) and range 2 is the other (R2A, R2B, R2C). Range 1 could be the weights (100, 10, 10) and range 2 could be the team’s occupancy percentage (30, 80, 60). What SUMPRODUCT does is it takes range1 and multiplies it by range2 and adds up the numbers: (R1A * R2A) + (R1B * R2B) + (R1C * R2C). You need to divide SUMPRODUCT by the sum of the weights to get the weighted average: SUMPRODUCT(range1, range2)/Sum(range1), where range1 is the weights (such as number of seats).

So, now…are you like the average person who uses average when you should be using weighted average?