How To Find Weighted Average In Excel

Have you ever think why we need an average or weightage average? In our daily, nosotros can't calculate everything, let'due south suppose I am request how's the weather for a week, yous are not going to tell me every day'south temperature, merely an interpretation hither comes to the concept of an Average. Mathematically you can evaluate the average=  Sum of Values/Number of Values, and the values carry equal weightage.

Now let's take another example you lot are in university, and you got your event of the semester test. Since the semester consists of minor and major where minor carries 25% weightage and major carries 75% of weightage, now how will you summate your average? Then here comes a concept of weightage boilerplate where we consider the weight of every value.

How To Calculate Weighted Boilerplate in Excel?

In order to calculate the weighted average, nosotros volition apply ii functions in Excel, the first i is the sum part, and the other SUMPRODUCT function, let's discuss it one by one.

SUM Part

Information technology is an inbuilt function in Excel, helps to sum the numerical data in a range of cells. The function enables united states of america to calculate multiple data very easily.

= SUM (number1, [number2],... )

For example, yous are purchasing class 12  science textbooks for altruistic to the NGO, so now you have to calculate the total number of books, and so here you only utilise the sum function.

SUMPRODUCT Function

It is an inbuilt function in Excel, it will render the sum of products of the value given in the argument or an array. This function usually uses when you demand to multiply many cells together.

= SUMPRODUCT ( array1, [array2],[array3],...)

For example, you are purchasing class 12 textbooks for donating to the NGO, and so now you take to calculate the total cost.  And so here's this function comes handy, you will kickoff enter assortment ane and the put comma and once more assortment 2 and so printing enter, yous will get your issue, which is 5780.

Now we are clear with the concept of both SUM function and SUMPRODUCT function at present let'south summate the weightage average. So in Excel to calculate weightage average is SUMPRODUCT/ SUM.

Weightage Average = SUMPRODUCT ( array1, [array2],[array3],...)/SUM (number1, [number2],...)        

 Let'due south sympathise with an example, you have to calculate your college marks, merely every field of study has a unlike average. And so we can use the weightage average to calculate our average marks.  So Now put the values in the formula which is:

          weightage average = SUMPRODUCT(B1:B6, C2: C6)/SUM(C2:C6)

 And then hit enter, you lot will get your result 82.25.

Instance of weightage average

Source: https://www.geeksforgeeks.org/how-to-calculate-weighted-average-in-excel/

Posted by: abbottowelast.blogspot.com

Share this post