Weighted Average in Excel (Table of Contents)
Introduction to Weighted Average in Excel
During a calculation of arithmetic mean or normal average, each number in a range has equal significance. Average value or arithmetic mean is calculated by adding a range of numbers together and then dividing this total by the number of values in the range
Definition of Weighted Average
Weighted average is the average where some datapoints or numbers are taken into consideration as the weighted elements to find out the average, instead of each of the data sets contributing equally to the final average, some data points contribute more than others in Weighted Average.
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.
4.9 (3,121 ratings)
View Course
Where it returns a total sales value of 64490.
Here, I need to calculate weighted average with the help of SUM Function.
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 –
