Demystifying the Weighted Mean

How to calculate a weighted mean in Excel

(or any spreadsheet)

Calculating a mean is easy -- kid stuff. Separating the children from the adults is the ability to calculate a weighted mean. Business people love weighted means for some reason, and if you surfed in here because somebody asked you to calculate a weighted mean and you have no idea how to do it, you've come to the right place.

In this article, my hope is to demystify and simplify the concept because it is simple and there's not a doggone mystical thing about it. If you can add, multiply, and divide, you can calculate a weighted average.

First of all, you need to understand what you're doing and why you're doing it. Wikipedia gives a simple explanation of weighted means. The reason this is so useful in business, mainly, is that not all customers are created equal, the old 80-20 rule. So if you were calculating average sales per customer, a weighted mean might give you a more accurate picture since the bigger customers account for a greater percentage of sales.

The most important factor in calculating a meaningful weighted mean is in choosing the metric to be used as weight. Make sure to ask yourself (or whomever asked you to calculate a weighted average) what the criterion for weighting should be. What's important here? The number of distribution outlets? Gross sales? Length of time on shelf? Linear footage? That's really up to you to decide. I find weight that can be measured in money to be most useful most of the time, at least in business. Academics are a different story; if you're doing real statistics and accounting for variance, uncertainty, probability and so forth, you probably don't need to be reading this article.

The easiest and -- arguably -- most accurate way to calculate a weighted mean is to use a convex coefficient (for practical purposes, just a fancy way of saying percentage of total) as the weight. The convex coefficients must sum to one, with each contributor to the total mean being assigned a unique convex coefficient based on their relative contribution to whatever total.

Confused? It's not that hard. Let me give a very simple real-worldish example.

Let's say your company sells products in three chain retailers: MalMart, J-Mart, and Barget. MalMart has a lot more stores and contributes far more to the total than do the other two, so we will assign a higher weight to MalMart in calculating our weighted average. Make sense? OK, good.

Your data breaks down like this:

Retailer | Gross Sales | Dist_Outlets

MalMart | $5,143,027.00 | 5500
Jmart | $1,246,211.00 | 856
Barget | $2,473,000.00 | 1300

Total: | $8,862,238.00

Now let's put that in our spreadsheet and smoke it.

If you calculate the plain-vanilla sales-per-store means (I assume you know how to do that), you'll find that MalMart has a per-store mean of $935.10; J-Mart, $1,455.85; and Barget, $1,902.31. Our mean in the total is $1,157.55.

Obviously, our biggest customer, MalMart, is not pulling its weight, but just how bad is it? Well, let's calculate the weighted average and find out. We'll use percentage of total gross sales as our weighting factor.

Use your handy dandy spreadsheet to calculate your convex coefficients (i.e., weight. Simply divide each retailer's total by the sum total of all of them). You'll find that w(MalMart)=.58, w(J-Mart)=.14 and w(Barget)=.28.

Now we're ready to calculate the vaunted weighted average, the Holy Grail of business reporting success. How exciting!

The basic formula for doing this is
(w[1]*m[1])+(w[2]*m[2])+(w[3]*m[3])... = weighted mean
where w[x]=weight and m[x]=mean

So we have (.58*$935.10)+(.14*$1455.85)+(.28*$1157.55)
EQUALS
(drum roll, please)
$1,278.72

That's our weighted average. MalMart is reeaaally slackin'. Now pass your report up and leave it to the smart people to figure out how to handle that situation.

Homework: Calculate the weighted average using the number of distribution outlets as your weighting factor. What did you find? Why?

Happy calculating!

Learn the secrets of Excel.

(affiliate link)