AVERAGE.WEIGHTED: Google Sheets Formulae Explained

The AVERAGE.WEIGHTED formula in Google Sheets is a powerful tool that can significantly enhance your data analysis capabilities. This formula allows you to calculate the average of a range of values, with each value having a different weight or importance. This is particularly useful in scenarios where all values in the dataset are not equally significant.

Understanding the AVERAGE.WEIGHTED Formula

The AVERAGE.WEIGHTED formula, as the name suggests, calculates the weighted average of a set of values. A weighted average is an average where each value has a different weight or importance. In Google Sheets, the AVERAGE.WEIGHTED formula is used to calculate this type of average.

It's important to note that the AVERAGE.WEIGHTED formula is not a built-in function in Google Sheets. Instead, it's a formula that you can create using the SUMPRODUCT and SUM functions. The formula looks like this: =SUMPRODUCT(range1, range2) / SUM(range2).

Components of the AVERAGE.WEIGHTED Formula

The AVERAGE.WEIGHTED formula consists of two main components: the SUMPRODUCT function and the SUM function. The SUMPRODUCT function multiplies the elements of two or more arrays and returns the sum of the products. In the context of the AVERAGE.WEIGHTED formula, the SUMPRODUCT function multiplies each value in the data set by its corresponding weight.

The SUM function, on the other hand, simply adds up all the values in an array. In the AVERAGE.WEIGHTED formula, the SUM function adds up all the weights. The result of the SUMPRODUCT function is then divided by the result of the SUM function to calculate the weighted average.

How to Use the AVERAGE.WEIGHTED Formula

Using the AVERAGE.WEIGHTED formula in Google Sheets is a straightforward process. The first step is to ensure that your data is organized in a way that each value has a corresponding weight. The values and weights should be in separate columns, but they should be in the same row.

Once your data is organized, you can then enter the AVERAGE.WEIGHTED formula. The formula should be entered in the cell where you want the weighted average to appear. Remember to replace 'range1' with the range of values and 'range2' with the range of weights.

Example of Using the AVERAGE.WEIGHTED Formula

Let's say you have a dataset of five products, each with a different price and quantity sold. You want to calculate the average price of the products, but you want to give more importance to the products that sold more units. In this case, you can use the AVERAGE.WEIGHTED formula.

The prices of the products are in column A (A2:A6), and the quantities sold are in column B (B2:B6). The AVERAGE.WEIGHTED formula would be: =SUMPRODUCT(A2:A6, B2:B6) / SUM(B2:B6). The result is the weighted average price of the products, giving more importance to the products that sold more units.

Benefits of Using the AVERAGE.WEIGHTED Formula

The AVERAGE.WEIGHTED formula offers several benefits. First, it allows you to calculate an average that takes into account the importance or weight of each value. This can provide a more accurate representation of your data, especially in scenarios where all values are not equally significant.

Second, the AVERAGE.WEIGHTED formula is flexible and can be used with any dataset, as long as each value has a corresponding weight. This makes it a versatile tool for data analysis in Google Sheets.

Limitations of the AVERAGE.WEIGHTED Formula

While the AVERAGE.WEIGHTED formula is powerful, it does have some limitations. The main limitation is that it requires each value to have a corresponding weight. If your data does not have weights, or if the weights are not known, you cannot use the AVERAGE.WEIGHTED formula.

Another limitation is that the AVERAGE.WEIGHTED formula can be complex to set up, especially for large datasets. However, with practice and understanding of the formula components, this complexity can be managed.

Conclusion

The AVERAGE.WEIGHTED formula in Google Sheets is a powerful tool for data analysis. It allows you to calculate the weighted average of a set of values, providing a more accurate representation of your data. While it has some limitations, its benefits outweigh them, making it a valuable tool for anyone working with data in Google Sheets.

Take Your Data Analysis Further with Causal

Now that you understand the power of the AVERAGE.WEIGHTED formula in Google Sheets, imagine what you could do with a tool designed specifically for data analysis. Causal is tailored for tasks like modelling, forecasting, and scenario planning, and brings your data to life with stunning visualizations and interactive dashboards. If you're ready to elevate your data game, sign up today for free and start exploring the possibilities with Causal.

Move beyond 

Google Sheets

Get started with Causal today.
Build models effortlessly, connect them directly to your data, and share them with interactive dashboards and beautiful visuals.