The AVERAGE.WEIGHTED function in Google Sheets is a powerful tool for calculating the weighted average of a range of values. This function is particularly useful in scenarios where certain values carry more importance than others, allowing for a more accurate summary of data. Whether in financial modeling, grading systems, or statistical analysis, this function can help you derive insights from aggregated data by considering the influence of various weights assigned to specific values.

Syntax

AVERAGE.WEIGHTED(values, weights)

• values: This argument represents the set of numerical values for which the weighted average is to be calculated.
• weights: This argument contains the weights that correspond to each value, determining their influence on the final average.

Example #1

AVERAGE.WEIGHTED(A1:A5, B1:B5)

This function will return the weighted average of the values in cells A1 to A5, using the weights in cells B1 to B5. For instance, if A1:A5 contains {10, 20, 30, 40, 50} and B1:B5 contains {1, 1, 1, 2, 2}, the result would be 36, meaning that the higher-weighted values cause the average to lean more in their direction.

Example #2

AVERAGE.WEIGHTED({80, 90, 95}, {0.2, 0.3, 0.5})

This function calculates the weighted average of scores 80, 90, and 95, with weights 0.2, 0.3, and 0.5 respectively. The result would be 91.5, indicating that the highest score has the most significant impact on the average due to its weight.

Example #3

AVERAGE.WEIGHTED(D1:D4, E1:E4)

In this instance, if the values in D1:D4 are {70, 85, 90, 100} and weights in E1:E4 are {0, 1, 1, 0}, the function will yield 87.5 as the weighted average, reflecting that only the second and third values influenced the final result, as indicated by their weights.

Error handling

• VALUE!: This error occurs if the lengths of the values and weights arrays do not match, indicating a misalignment between the data sets.
• DIV/0!: This error appears when all weights are zero, suggesting that an average cannot be computed without any contribution from weights.
• NUM!: This error indicates an out-of-bounds or inappropriate weight, typically if a weight is negative or if the input is not numerical.

Conclusion

In summary, the AVERAGE.WEIGHTED function in Google Sheets is an essential feature for anyone needing to compute weighted averages effectively. By understanding its syntax and applying it correctly, users can derive more nuanced insights from their data, enhancing decision-making processes. Whether for academic, business, or personal projects, this function proves to be an invaluable asset in any data analyst’s toolkit.