Weighted Average In Excel

What Is Weighted Average In Excel?

The Weighted Average in Excel calculates the average of the numerical values, where the values contain assigned weighted percentage, from the overall percentage. The Weighted Average is calculated by finding the SUMPRODUCT of the values [multiply each value with its assigned weighted percentage] and the SUM of the weighted percentages and, finally, by dividing the SUMPRODUCT by the SUM.

Key Takeaways
  • The Weighted Average in Excel helps the users find the mean of the numerical values containing the weight percentage.
  • It is reliable because it gives more accurate results than the simple Average function.
  • If the dataset contains text in the array range, the Weighted Average Formula considers the data value as 0.
  • The Weighted Average in Excel function takes into account only the numerical values. So, when we assign a value for a text, then by the (–) double negative, Excel will convert “True” into 1 and “False” into 0. Then, we can proceed with the calculation.

Weighted Average Formula in Excel

The syntax of the Weighted Average formula in Excel is,

= SUMPRODUCT(array1,[array2],[array3],…) / SUM(number1,[number2],..)

How To Calculate Weighted Average In Excel?

We can Calculate Weighted Average in Excel in two ways,

  1. By manually selecting the excel cell reference with the values and multiplying it with its assigned weighted percentage.
  2. By using the Weighted Average formula in Excel.

Lets take a basic example where we Calculate Weighted Average in Excel without the use of formula.

In the table, the data is, 

  • Column A contains Values.
  • Column B contains Weight.
Weighted Average In Excel Basic Example

The steps to Calculate Weighted Average In Excel are as follows:

Step 1: Select cell B6 and enter the formula, =B2+B3+B4+B5. We get the output as “10” i.e., the weight total.

Excel Basic Example.1

Step 2: Select the cell B7 and enter the formula, =(A2*B2+A3*B3+A4*B4+A5*B5)/B6

Excel Basic Example.2

The output is shown above. The result of the Weighted Average is 60.

#1 SUM Formula For Weighted Average

The SUM excel function adds the values in a cell range and returns the sum.

The syntax of the SUM Formula for Weighted Average is,

=SUM(number1,[number2],..)

The arguments of the SUM Formula for Weighted Average are,

  • number1 – We will add the first numeric value. It is a mandatory argument.
  • number2 – We will add the second numeric value. It is an optional argument.

#2 SUMPRODUCT Formula For Weighted Average

The SUMPRODUCT function multiplies the values in an array range and returns the sum of those products.

The syntax of the SUMPRODUCT formula for Weighted Average is,

     =SUMPRODUCT(array1,[array2],[array3],…)

The arguments of the SUMPRODUCT formula for Weighted Average are,

  • array1 – We will find the product of the first numeric range or array values and add them. It is a mandatory argument.
  • array2 – We will find the product of the second numeric range or array values and add them. It is a mandatory argument.
  • array3 – We will find the product of the third numeric range or array values and add them. It is an optional argument.

Examples

We will understand the Weighted Average in Excel with some advanced scenarios.

Example #1 – Weighted Average using SUMPRODUCT and SUM formula

We will find the Weighted Average using SUMPRODUCT and SUM formula.

In the table, the data is, 

  • Column A contains Items.
  • Column B contains Values.
  • Column C contains Weight.
Weighted Average In Excel Example 1

The steps to find the Weighted Average of the given values are as follows:

1: First, select cell C6 to find the SUMPRODUCT.

Weighted Average In Excel Example 1.1

2: In cell C6, enter the formula, =SUMPRODUCT(B2:B5,C2:C5) and press the “Enter” key.

Weighted Average In Excel Example 1.3

3: The result of the SUMPRODUCT is ‘200’, as shown below.

Weighted Average In Excel Example 1.2

4: Next, select cell C7 to find the SUM.

Example 1.4

5: In cell C7, enter the formula, =SUM(C2:C5) and press the “Enter” key.

Example 1.5

6: The result of the SUM is ‘10’, as shown below.

 Excel Example 1.6

7: Now, select cell C8 to find the Weighted Average using SUMPRODUCT and SUM formula.

Weighted Average In Excel Example 1.7

8: In cell C8, enter the formula, =C6/C7 [ i.e., SUMPRODUCT / SUM], or select the cell references and press the “Enter” key.

Example 1.8

9: The output is ‘20’, as shown below.

Example 1.9

Example #2 – Weighted Average with 0’s & 1’s value of SUMPRODUCT

We will find the Weighted Average with 0’s & 1’s value of the SUMPRODUCT formula.

In the table, the data is, 

  • Column A contains Month.
  • Column B contains Sales.
  • Column C contains Region.
  • Column D contains the original value of Region.

The tabular explanation of this example is as follows:

MonthSalesRegionThe original value of the Region
Jan450NorthTrue
Feb300WestFalse
Mar600NorthTrue
Apr550SouthFalse
May400NorthTrue
June700EastFalse

In the SUMPRODUCT function, we have assigned the formula to the text value “North”, and so the text assigned will have “True” or “False” values.

The function only uses the numerical values, so by the (–) double negative, Excel converts “True” into 1 and “False” into 0, as explained below.

[So, the value will return as 1 for “True” only for “North”]

SalesRegionSUMPRODUCT
4501450 * 1 = 450
3000300 * 0 = 0
6001600 * 1 = 600
5500550 * 0 = 0
4001400 * 1 = 400
7000700 * 0 = 0
 Total of SUM = 3Total = 1450
Weighted AverageSUMPRODUCT / SUM1450 / 3 = 483.333

The steps to calculate the Weighted Average of the given values are as follows:

Weighted Average In Excel Example 2.1

Step 1: First, in cell D8, enter the formula, =SUMPRODUCT(–(C2:C7=“North”),B2:B7)

Example 2.2

Step 2: Press the “Enter” key. The result of the SUMPRODUCT is “1450”, as shown below.

Excel Example 2.3

Step 3: Next, in cell D9, enter the formula, =SUM(D2:D7)  

Excel Example 2.4

Step 4: Press the “Enter” key. The result of the SUM is “3”, as shown below.

Excel Example 2.5

Step 5: Finally, in cell D10, enter the formula, =D8/D9 [i.e., the formula for calculating the Weighted Average in Excel, SUMPRODUCT / SUM].

Weighted Average In Excel Example 2.6

Step 6: Press the “Enter” key. The Weighted Average result is “483.333”, as shown below.

Excel Example 2.7

Important Things To Note

  • We can calculate the Weighted Average in Excel using the SUMPRODUCT function and the SUM functions or by selecting the cell references with the values and multiplying them with the assigned weighted percentage.
  • The stock investors use it to track the cost of the shares.
  • The Weighted Average in Excel will return a “#VALUE!” error if the array arguments in the SUMPRODUCT function are of different sizes.

Frequently Asked Questions 

What is the meaning of Weighted Average in Excel?


The Weighted Average in Excel means providing the desired weights to each of the values available for calculating the average.

What is the formula for Weighted Average in Excel?


The Weighted Average formula in Excel is,

= SUMPRODUCT(array1,[array2],[array3],…) / SUM(number1,[number2],..)

How weighted average is calculated?


A few ways to Calculate Weighted Average in Excel are as follows:

1) We can select the cell reference with the values and multiply it with its assigned weighted percentage.
2) We can use the Weighted Average formula [SUMPRODUCT and the SUM function]. We use the SUMPRODUCT formula to get the product of all the number range with available weights, find its sum, and then divide that with the actual SUM of the available weights.
3) We can use the AVERAGE function and multiply the numbers by their weightage.

Download Template

This article must help understand the Weighted Average in Excel with its formula and examples. You can download the template here to use it instantly.

This has been a guide to Weighted Average In Excel. Here we will calculate it manually & with the formula, along with examples & downloadable excel template. You can learn more from the following articles –

Reader Interactions

Leave a Reply

Your email address will not be published.