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
where:
- 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.
Additional Resources
The following tutorials explain how to perform other common tasks in VBA:
VBA: How to Write AVERAGEIF and AVERAGEIFS Functions
VBA: How to Write SUMIF and SUMIFS Functions
VBA: How to Write COUNTIF and COUNTIFS Functions