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 the 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 numbers 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
The 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 the help of the SUM function.
Where it returns a total sales value of 64490.
Here, I need to calculate the weighted average with the help of the SUM Function.
The below-mentioned principle is used; you have to multiply each value (the price of the 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 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 the 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).
23 Online Courses | 9 Hands-on Projects| 110+ Hours| Verifiable Certificate of Completion
4.9
View Course
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 the brand name, column B (the price of each brand), column C (quantity sold) & column D (Sales value).
Here, I need to calculate the weighted average with the help of the SUMPRODUCT & SUM Function. SUMPRODUCT function multiplies the dataset of two or more arrays and then adds the products. Let’s apply both the 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 the 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.
A 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 of different sizes. 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 averages in Excel along with practical examples and a downloadable excel template. You can also go through our other suggested articles –
