=SUMPRODUCT (numbers, weights)/SUM (weights)
With Excel, you can also calculate a weighted average of a given data set. Using the useful features provided by Excel can bring a variety of benefits. The formula used to perform this task consists of a combination of two functions.
The SUM function and the SUMPRODUCT function are used together in a formula to calculate a weighted average. This formula has been shown in the example in cell G7.
The work of this formula involves multiplying the array and adding the results. In the given example, the total number when using the SUMPRODUCT function returns the corresponding weight. After that, the numbers obtained by adding the weights calculated by the Excel formula SUM will be used. This formula has a very exciting feature, that is, it does not need to consider weights, because they do not matter. For this formula, you can choose any number that is relevant to your case and requirements.
Figure1. Example of Weighted average