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?
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.
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:
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.
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.