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)
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)
$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
Above, we first use the
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...