Finding a Weighted Average in Excel
Calculating a weighted average is possible in Excel using the
What is a weighted average?
A weighted average is an
If we wanted to calculate the simple, non-weighted
=AVERAGE ($10.00 ,$2.00 ,$1.00 )
Output:$4.33
But the above formula doesn't really make sense: the average price per item sold isn't really
To solve this problem, we use a weighted average.
The weighted average formula
The mathematical formula for taking a weighted average is as follows:
((value_1 *weight_1 ) + (value_2 *weight_2 )...)/(sum_of_weights )
In plain English, to find the weighted average:
- Take the product of each value times its weight;
- Sum all of these products together; and
- Divide by the sum of the weightings.
Let's apply this formula to our SnackWorld example above to find the weighted average price per item sold. Remember, SnackWorld sold
= (($10 *1 ) + ($2 *10 ) + ($1 *20 )) / (1 +10 +20 )
= ($10 +$20 +$20 ) / (31 )
=$50 /31
=$1.61 / item
That sounds more like it — our weighted average cost per item sold is
Applying this to Excel
So how do we calculate a weighted average in Excel? If you're familiar with the
Here's an example of the
=SUMPRODUCT (C3:C5 ,D3:D5 )/SUM (D3:D5 )
Step 1: =((C3 *D3 ) + (C4 *D4 ) + (C5 *D5 )) /SUM (D3:D5 )
Step 2: =($10 +$20 +$20 ) /31
Output:$1.61
Above, we first use the
This (
Have questions or comments on weighted averages? Sound off in the comments section below!
Save an hour of work a day with these 5 advanced Excel tricks
Work smarter, not harder. Sign up for our 5-day mini-course to receive must-learn lessons on getting Excel to do your work for you.
- How to create beautiful table formatting instantly...
- Why to rethink the way you do VLOOKUPs...
- Plus, we'll reveal why you shouldn't use PivotTables and what to use instead...