To calculate a weighted average in Excel, the most efficient and common method is to use the SUMPRODUCT and SUM functions together. Here's how to do it step-by-step:

Steps to Calculate Weighted Average in Excel

1. Prepare your data:

   • In one column, list the values you want to average (e.g., scores, prices).
   • In the adjacent column, list the corresponding weights for each value (e.g., importance, quantity).

2. Use the formula:

   =SUMPRODUCT(values_range, weights_range) / SUM(weights_range)
   

   • SUMPRODUCT(values_range, weights_range) multiplies each value by its weight and sums the results.
   • SUM(weights_range) sums all the weights.
   • Dividing these gives the weighted average.

Example

If your values are in cells B2:B6 and weights in C2:C6, enter this formula in another cell:

=SUMPRODUCT(B2:B6, C2:C6) / SUM(C2:C6)

This will calculate the weighted average of the values in B2:B6 according to the weights in C2:C6

Manual Method (for understanding)

• Multiply each value by its weight in a new column.
• Sum all these products.
• Sum all the weights.
• Divide the sum of products by the sum of weights.

This manual approach helps understand the concept before using the formula

. This method is straightforward, efficient, and works well for any size dataset in Excel.