# Weighted Average

I’ll show you the weighted average formula, how it calculates, and I’ll prove that the answer is correct!

### Weighted Average Formula

Column B has the count. Column C has the weight. Weighted average formula is:

=SUMPRODUCT(B2:B12,C2:C12)/SUM(B2:B12)

### Why Weighted Average?

Why can’t we just use a normal average?

If we average the numbers in column C we get an average of 14.0

BUT….it wouldn’t fairly represent our group of dogs.

Look at Pic 1, does an average of 14.0 look right???

Remember: 1 dog weighs 8 pounds, 7 weigh 9 pounds, 10 weigh 11 pounds, 11 weigh 12 pounds etc.

=AVERAGE(C2:C12) ignores the fact that most dogs weigh 9, 11, or 12 pounds (that’s 28 of 40 dogs). Only 7 dogs weigh 14 or more pounds.

Column D below helps to visualize this. I changed the font size to reflect their weight.

### Weighted Average Logic

This is our weighted average formula:

=SUMPRODUCT(B2:B12,C2:C12)/SUM(B2:B12)

Let’s examine each part separately:

SUMPRODUCT(B2:B12,C2:C12)

SUMPRODUCT does this (1 dog X 8 pounds) + (7 dogs X 9 pounds) + (10 dogs X 11 pounds) etc   =482

SUM(B2:B12)

SUM is simply counting the dogs.  =40

SUMPRODUCT (482) divided by SUM (40) gives us the correct weighted average of 12.05

### Weighted Average Proof!

We were given the summary of the original data(Pic 1). Let’s recreate the data!

Step 1 Cumulative Sum: column A has a cumulative sum (+1) of column B dog count values. This creates binning groups.

Step 2 Counter: a sequential counter from 1 to 40 (40 dogs). Represents dog #1, dog #2, dog #3, etc.

Step 3 Binning: =MATCH(M2,\$A\$1:\$A\$13,TRUE) finds each counter value in column A. TRUE (approximate match) not FALSE (exact match) bins each counter number.

Pic 4 shows that dog #1 weighs 8 pounds. Dogs 2,3,4,5,6,7,8 all weigh 9 pounds (group or bin 2).

Look at counter value of 9. Dog #9 falls into bin 3 and has a weight of 11 pounds.

The Proof: Use the normal average on column O. It’s the same as the weighted average!

### Dog Histogram How did I create it?

I used formula =REPT(“õ”,B2)

Cell font = Webdings

I manually changed the font size (vba would be better!)

My inner nerd took over my body:

I changed the formula to:  =REPT(VLOOKUP(\$V\$1,’Animal List’!\$A\$2:\$B\$5,2,0),B2)

In cell V1 select from a list (maybe you have pet squirrels !!)

NOTE: if you increase the dog counts in column B then extend formulas in columns M,N, and O.

### Weighted Average Video

Watch this video from Microsoft.

### My Dogs

Cali (on the right) weighs 10 pounds and Fenton weighs approximately 14 pounds (he wiggles a lot on the scale at the vet). That’s me in the middle. I weigh a lot more! My name is Kevin Lehrbass. We live in Markham Ontario Canada.

I’ve been a Data Analyst since 2001. Microsoft Excel is my favorite software but Cali & Fenton get mad at me if I spend too much time in Excel.

Their hobbies: getting treats, barking at squirrels, naps with me on the couch.