Introduction to Weighted Average in Excel
Weighted Average in Excel allows the user to provide the recommended or desired weights to each of the values available for which we want to calculate average. This we can do using the AVERAGE function and multiplying the numbers with weightage. And also with the help of the SUMPRODUCT function, we can find a weighted average in Excel. We can use SUMPRODUCT to get the sum of all the numbers and products with available weights and then divide that with the actual sum of number again.
How to Calculate Weighted Average in Excel?
Let’s check out the various available options to calculate a weighted average.
Example #1 – Calculation with the help of Sum Function
Sum function is used to calculate the weighted average if the table contains a smaller number of datasets.
In the below-mentioned example, I have a dataset in column A which contains the brand name, column B (Price of each Brand), column C (Quantity Sold) & column D (Sales Value).
Initially, for reference purpose, you can calculate the total quantity sold in the cell “C7” with the help of sum function for a reference purpose. i.e. =SUM (C2:C6) returns a value 68.
Similarly, you can calculate the total sales value in the cell “D7” with help of the SUM function.
Where it returns a total sales value of 64490.
Here, I need to calculate weighted average with the help of SUM Function.
4.9 (4,823 ratings)
View Course
The below-mentioned principle is used, you have to multiply each value (price of brand) by its corresponding quantity sold and then add all the results together. Later you have to divide this answer by the sum of the quantity sold. Let’s apply the sum function in the cell “C8”
=SUM(B2*C2, B3*C3, B4*C4, B5*C5, B6*C6,)/SUM(C2:C6). It returns the weighted average value i.e. 948.38.
To find out the difference between Normal and Weighted Average, you can calculate the normal average in the cell C9, i.e. =AVERAGE (B2:B6).
It returns a value of 1036.
You can check the accuracy level of both calculations i.e. normal & weighted average by below-mentioned process. Normally, when you multiply 1036 (Normal Average Value) with 68 (Total Quantity Sold), you will get 70448.
Which is not equal to the total sales value (H2 cell), but when you multiply 948.38 (weighted average value) with 68 (total quantity sold) you will get 64490 which is equal to the total sales value H3 cell).
Example #2 – Calculating with SUMPRODUCT and SUM Function
SUMPRODUCT allows you or helps out to efficiently & quickly perform weighted average calculations when you are working with a large number of data sets.
In the below-mentioned example, I have a dataset in column A which contains brand name, column B (price of each brand), column C (quantity sold) & column D (Sales value).
Here, I need to calculate weighted average with the help of SUMPRODUCT & SUM Function. SUMPRODUCT function multiplies the dataset of two or more arrays and then adds the products. Let’s apply both sum-product & sum function in the cell “C7”.
=SUMPRODUCT (B2:B6, C2:C6) / SUM (C2:C6) return’s weighted average 948.38.
We can see, how this formula works with a breakdown when compared to 1st example. So, here instead of multiplying brand price by its quantity sold individually, I supplied two arrays in the SUMPRODUCT function (in this case, an array is a continuous range of cells i.e. two arrays, I have used brand price & quantity sold (B2:C2, B6:C6) array and then divide that result by the sum of quantity sold.
Disadvantage of Weighted Average: Sum functions are not a better option to calculate the weighted average value if you have a large number of datasets or elements in a table
Things to Remember About Weighted Average in Excel
- SUMPRODUCT returns the #VALUE! error value, if array arguments are off different size. Therefore, all array arguments must be of the same size or the same number of datasets should be present in each array.
- If the datasets contain text data in a range, then SUMPRODUCT treats or considers them as zeros.
Recommended Articles
This is a guide to Weighted Average in Excel. Here we discuss How to calculate Weighted Average in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –
