Usually when you compute an average, all of the numbers are provided equivalent significance; the numbers are added together, and after that, divided by the number of numbers. With a Weighted Average, one or more numbers is given a greater significance, or weight.
Find a Weighted Average
Use the SUMPRODUCT and the SUM functions to discover a Weighted Average, which depends on the weight used to the worths.
-
For example, a delivery of 10 cases of pencils is 20 cents per case. But a second shipment of 40 cases costs 30 cents per case, due to the fact that pencils are in high need. If you averaged the cost of each shipment by doing this (0.20 +0.30)/ 2 = 0.25, the result isn’t precise.
The math does not take into consideration that there are more cases being cost 30 cents than at 20 cents. To get the proper average, use this formula to get the outcome (28 cents per shipment):
=SUMPRODUCT(A2: A3, B2: B3)/ AMOUNT(B2: B3)
The formula works by dividing the overall cost of the two orders by the total variety of cases ordered.
Desire more?
Determine the average of a group of numbers
typical function
AVERAGEIF function
Typically when you compute an average, all of the numbers are given equivalent significance.
The numbers are combined and after that divided by the variety of numbers, as in this example, which returns an unweighted average of 5.
With a Weighted Average, one or more numbers is offered a higher significance, or weight.
In this example, the Mid-term and Last tests have a greater weight than Tests 1 and 2.
We’ll utilize the SUMPRODUCT and SUM functions to determine the Weighted Average.
The SUMPRODUCT function multiplies each Test’s rating by its weight, and then, includes these resulting numbers.
We then divide the result of SUMPRODUCT by the AMOUNT of the weights.
And this returns the Weighted Average of 80.
SUMPRODUCT is basically the Amount of Test 1 times its weight, plus the Mid-term times its weight, and so on.
To get the Weighted Average, you divide by the Overall of the weights.
If we had actually just averaged the Test scores, the value would be 75.5, a significant difference.
To find out more about the SUMPRODUCT and SUM functions, see the course summary.
Now, you have a good concept about how to average numbers in Excel.
Of course, there’s constantly more to find out.
So, have a look at the course summary at the end, and most importantly, explore Excel 2013 on your own.
Training: Generally when you compute an average, all of the numbers are provided equivalent significance; the numbers are added together and then divided by the variety of numbers. With a weighted average, several numbers is offered a higher significance, or weight.
