# 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 = Σ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.