Table of Contents
To calculate a weighted average in VBA, you need to multiply each value in your range by the corresponding weight factor, then sum the products and divide by the total of all weight values. This can be done with a simple formula in VBA, such as SUMPRODUCT(range, weight factor) / SUM(weight factor). This will give you the weighted average of the range you have provided.
You can use the following basic syntax to calculate a weighted average using VBA:
Sub FindWeightedAverage()
Range("E2") = _
WorksheetFunction.SumProduct(Range("B2:B7"), Range("C2:C7")) / _
WorksheetFunction.Sum(Range("C2:C7"))
End Sub
This particular example calculates a weighted average using cells in the range B2:B7 as the values and cells in the range C2:C7 as the weights.
The weighted average is then displayed in cell E2.
The following example shows how to use this syntax in practice.
Example: Calculate Weighted Average Using VBA
Suppose we would have the following dataset in Excel that shows various sales made by employees at some company:
Suppose we would like to calculate the weighted average of price, using the values in the Amount column as the values and the values in the Price column as the weights:
We can create the following macro to do so:
Sub FindWeightedAverage()
Range("E2") = _
WorksheetFunction.SumProduct(Range("B2:B7"), Range("C2:C7")) / _
WorksheetFunction.Sum(Range("C2:C7"))
End Sub
When we run this macro, we receive the following output:
Notice that cell E2 contains a value of 9.705882.
We can verify this is correct by manually computing the weighted average.
Recall that we use the following formula for weighed average:
Weighed Average = ΣwiXi / Σwi
- wi = the weight values
- Xi = the data values
We can plug in the values from our dataset into this formula to calculate the weighted average of price:
- Weighed Average = ΣwiXi / Σwi
- Weighed Average = (1*8 + 3*5 + 2*6 + 2*7 + 5*12 + 4*14) / (1+3+2+2+5+4)
- Weighed Average for Student A = 9.705882
This matches the value that we calculated using the macro.