Weights used are the quantity weights. Weighted Average in excel is calculated when we assign each data set with some kind of weights like weightage in statistics or portfolios we use weighted average to calculate more robust and impact observations and calculations the formula for weighted average is very simple which is w1x1w2x2wnxn w1w2wn where w is the weight assigned to the x value and we use sumproduct function to calculate the weighted average. The basic formula for a weighted average where the weights add up to 1 is x1 w1 x2 w2 x3 w3 and so on where x is each number in your set and w is the corresponding weighting factor.
Average of Price Relative Method using the geometric mean.
The formula for finding the weighted average is the sum of all the variables multiplied by its weight then divided by the sum of the weights. Then the total amount paid for the shares 3000 in this case is divided by the number of shares acquired over both years 150 to get the weighted average price paid of 20. Weighted Average in Excel. Weighted average-of-relatives index can be calculated by taking arithmetic mean as well as geometric mean as average.