Skip to content

Perform Weighted Average Calculations Using Excel’s SUMPRODUCT Function

    Weighted Average Calculations

    A normal average calculation sums values and then divides by the number of values.  This basic average calculation works fine unless you need to give a different weight to each value.  For example, our students take 5 exams a year and we need to calculate their overall percentage at the end of the year.

    Each exam is weighted differently: term 1 exam is 15% of the overall year’s mark, term 2, 20%, term 3, 25% and so on. To create an accurate end of year mark we need to take the weighting given to each exam into consideration.  We need to calculate a weighted average.

    Weighted Average Calculation

    To perform a weighted average calculation you multiply each value (percentage mark) by its corresponding weight and then add all the results together. You then divide this answer by the sum of the weights.

    So for Bob, we would calculate his weighted average like this…

    (56%*15% + 17%*20% + 36%*25% + 68%*30% + 46%*10%) / (15%+20%+25%+30%+10%)

    Using SUMPRODUCT

    As you might appreciate the weighted average calculation could get quite long and tedious if you have lots of the values and weights.  Thankfully Excel’s SUMPRODUCT function will help you to perform the calculation quickly even if you have thousands of values.

    As its name suggests the SUMPRODUCT function sums the product of multiple arrays

    =SUMPRODUCT(array1, [array2], [array3], ...)

    – well what does this mean?

    In this context the two arrays are the cells that contain the student marks and the cells that contain the weights given to each exam.

    The product is calculated by multiplying corresponding values in each array – so the mark achieved multiplied by its corresponding weighting. SUMPRODUCT then sums up all the product calculations.

    Now if you look at our original calculation…

    (56%*15% + 17%*20% + 36%*25% + 68%*30% + 46%*10%) / (15%+20%+25%+30%+10%)

    …you can see that in the first part (before the division), that’s exactly the calculation we have performed, but in long hand.  This is what the calculation would look like using SUMPRODUCT (we have also used SUM to add up the weights).

    =SUMPRODUCT(M7:Q7,$M$5:$Q$5)/SUM($M$4:$Q$5)

    And here are our end results.

    SUMPRODUCT allows you to quickly and efficiently perform weighted average calculations even when you are working with large data sets.