Excel: Weighted average - how to calculate it

You can use functions and formulas to automate many calculations and other processes in Microsoft Excel. If you want to calculate the weighted average, you can use the SUM PRODUCT and SUM functions.

Calculate weighted average: It works with these formulas

When calculating the mean, all numbers are usually assigned the same weight. All numbers are added together and then divided by the total number. The result is then the unweighted mean. With the AVERAGE function, you can easily calculate exactly this value.

• The syntax for the formula is simple: "=AVERAGE(number)". The argument "number" you can replace here by the values from which the mean value is to be formed or the cells in which the value is located.

When calculating the weighted average, however, one or more numbers have a higher weighting than others. In order for you to calculate the weighted average in Excel, you must combine the SUMPRODUCT and SUM functions.

• The syntax for nesting the two functions is as follows:"=SUMPRODUCT(values;weight)/SUM(weight)".

This is what you have to pay attention to when calculating the weighted mean

To avoid errors or problems during the calculation, you should make sure that the weighting always adds up to 100 percent or 1. Also, make sure that all values are entered as digits, otherwise you may get an error message or the cells will simply be ignored, like cells without content.