We need to process data such that they will yield a score that is the sum of weighted averages. One familiar example are scores and grades at school:
A useful function for this purpose is the AVERAGE.WEIGHTED function. Read this article to learn more about how to use this function and how it compares with SUMPRODUCT, a related but different function. Are you ready?
How to use AVERAGE.WEIGHTED function
Relative Weights
When we want to calculate the weighted averages, we need to set a column or a row to hold the relative weights of each data. For our example, we will store the relative weights as follows in Row 1:
As you may point out, we normally store weights as either decimals or percentages rather than whole numbers as in this example. We will point out why we use this format later on.
AVERAGE.WEIGHTED Syntax
The AVERAGE.WEIGHTED function has the following syntax:
AVERAGE.WEIGHTED(values, weights, [additional values], [additional weights])
Where values refer to the actual values or scores, while weights refer to the corresponding weights of the values. You can include the actual values and weights in the function or point it to a cell or array. We will point the function to the data as follows:
Here are the examples of AVERAGE.WEIGHTED syntaxes, explained:
- average.weighted(B3,B1,C3,C1,D3,D1,E3,E1,F3,F1) - the value and their weights are individually listed in pairs
- average.weighted(B3:F3,B1:F1) - the range of values are specified first and then the range of their corresponding weights. This works if there is a clear correspondence, such as when the values are stored in a single row and the weights are on another row and the pairs are in the same column, just like in the example.
- average.weighted(B3:F3,$B$1:$F$1) - same as the second example, but the range of weights are fixed. This is useful when you want to simply drag down the equation to be copied to other rows, for example.
The third example is especially useful if you have either rows or columns of entries where the same AVERAGE.WEIGHTED function needs to be applied, as shown below, because the weights will stay the same in each calculation and only the values will change.
AVERAGE.WEIGHTED vs SUMPRODUCT function
As you might have noticed, another similar function is the SUMPRODUCT function. There is an important difference between the two, however. It may not be apparent if you store the relative weights of values in the form of decimals or percentages, as show below:
If you store them in whole numbers, however, the difference emerges:
The AVERAGE.WEIGHTED function is superior in this regard because it automatically adjusts the relative weights in whole numbers into its decimals or percentages, and then proceeds to calculate the weighted average of the values.
Sometimes you need to adjust the weights over time, and it can take some time to calculate their decimal or percentage equivalents. AVERAGE.WEIGHTED automatically handles it so you can go on with your analysis of the results.
Recommended articles
More resources
