How to Calculate Weighted Average in VBA (With Example)

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 = Σw_iX_i / Σw_i

where:

• w_i = the weight values
• X_i = the data values

We can plug in the values from our dataset into this formula to calculate the weighted average of price:

• Weighed Average = Σw_iX_i / Σw_i
• 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