Check Sheet Logo
Check Sheet

AVERAGE.WEIGHTED

The AVERAGE.WEIGHTED formula 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.

Usage

Use the AVERAGE.WEIGHTED formula with the syntax shown below, it has 2 required parameters and 2 optional parameters:

=AVERAGE.WEIGHTED(values, weights, [additional values], [additional weights])
Parameters:
  1. values (required):
    The range of cells or array of values to calculate the weighted average for.
  2. weights (required):
    The range of cells or array of weights corresponding to each value in the values parameter.
  3. additional values (optional):
    Optional additional ranges of cells or arrays of values to include in the calculation.
  4. additional weights (optional):
    Optional additional ranges of cells or arrays of weights corresponding to each value in the additional values parameter.

Examples

Here are a few example use cases that explain how to use the AVERAGE.WEIGHTED formula 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.

Common Mistakes

AVERAGE.WEIGHTED not working? Here are some common mistakes people make when using the AVERAGE.WEIGHTED Google 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.

Related Formulas

The following functions are similar to AVERAGE.WEIGHTED or are often used with it in a formula:

  • AVERAGE

    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.

  • SUM

    The SUM function in Google Sheets adds up a range of numbers. This function is most commonly used to sum the values in a range of cells.

  • PRODUCT

    The PRODUCT formula 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.

  • COUNT

    The COUNT formula 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.

  • IF

    The IF formula 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.

Learn More

You can learn more about the AVERAGE.WEIGHTED Google Sheets function on Google Support.