AVERAGE.WEIGHTEDformula calculates the weighted average of a set of values, where each value is multiplied by its corresponding weight and then the sum of these products is divided by the sum of the weights. This formula is commonly used to calculate average grades, financial returns, or other values where weights are assigned to each data point.
- How to use
- Examples of using
AVERAGE.WEIGHTEDformula not working?
- Similar formulas to
AVERAGE.WEIGHTED formula with the syntax shown below, it has 2 required parameters and 2 optional parameters:
- values (required):
The range of cells or array of values to calculate the weighted average for.
- weights (required):
The range of cells or array of weights corresponding to each value in the values parameter.
- additional values (optional):
Optional additional ranges of cells or arrays of values to include in the calculation.
- additional weights (optional):
Optional additional ranges of cells or arrays of weights corresponding to each value in the additional values parameter.
ExamplesHere are a few example use cases that explain how to use the
AVERAGE.WEIGHTEDformula in Google Sheets.
Weighted grade calculation
Calculate a student's final grade based on their weighted scores in different assignments and tests.
Portfolio return calculation
Calculate the weighted average return on a portfolio based on the returns of different stocks or investments and their corresponding weights.
Sales performance calculation
Calculate the average sales price of a product, where each sale has a different weight based on the quantity sold or revenue generated.
AVERAGE.WEIGHTEDnot working? Here are some common mistakes people make when using the
AVERAGE.WEIGHTEDGoogle Sheets Formula:
Not using weights that add up to 1 or 100%
One common mistake when using the
AVERAGE.WEIGHTED formula is to use weights that do not add up to 1 or 100%. This will result in an incorrect calculation of the weighted average.
Not multiplying values by weights
Another common mistake when using the
AVERAGE.WEIGHTED formula is to forget to multiply each value by its corresponding weight before summing them. This will also result in an incorrect calculation of the weighted average.
Using non-numeric values as weights
The weights used in the
AVERAGE.WEIGHTED formula must be numeric values. Using non-numeric values, such as text or logical values, will result in an error.
The following functions are similar to
AVERAGE.WEIGHTED or are often used with it in a formula:
The AVERAGE function calculates the average (arithmetic mean) of the values passed to it. It is commonly used to find the average of a range of cells containing numerical data.
SUMfunction in Google Sheets adds up a range of numbers. This function is most commonly used to sum the values in a range of cells.
PRODUCTformula in Google Sheets multiplies all the numbers given in the arguments and returns the product. It is commonly used to calculate the total product of a range of cells.
COUNTformula returns the number of numeric values within a specified range of cells. It is commonly used to count the number of entries in a given range that meet specific criteria, such as counting the number of sales above a certain threshold.
IFformula is a logical function used to test a condition and return one value if the condition is true and another value if the condition is false. It is commonly used to create conditional statements and perform calculations based on certain conditions.
You can learn more about the
AVERAGE.WEIGHTED Google Sheets function on Google Support.