How to Calculate Weighted Average with Percentages in Excel

In this article, I will show you how to calculate the weighted average formula in Excel with percentages. The weighted average is the average where some numbers are taken into consideration as the weighted elements for finding the average. It differs from the normal average as the weighted elements contribute more heavily to the final result than the other elements. Here I will calculate the weighted average with percentages.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


2 Suitable Methods to Calculate Weighted Average with Percentages in Excel

In the following I have shared 2 simple methods to calculate weighted average with percentages in excel. Stay tuned!


1. Use SUM Function to Calculate Weighted Average with Percentages

Let`s say you achieved different numbers in different subjects. You need to carry out the weighted average where different weights are given in different subjects. Now we will calculate the weighted average with percentages in excel using the SUM function.

1.1 Single Term

With Proper utilization of the SUM function you can easily calculate the weighted average if you have single terms. To do so-

Steps:

  • Starting with, select a cell (D12) to apply the formula.
  • Write the formula down-
=SUM(C5*D5,C6*D6,C7*D7,C8*D8,C9*D9,C10*D10)/SUM(D5:D10)

Use SUM Function to Calculate Weighted Average with Percentages in Excel

  • Therefore press Enter.
  • Finally, we have successfully calculated the weighted average with percentages using a simple formula.

Use SUM Function to Calculate Weighted Average with Percentages in Excel

1.2 Multiple Terms

In some cases you will find multiple terms just like the following screenshot.

Hence, you can use the SUM function to determine the weighted average. Follow the below steps-

Steps:

  • In the same fashion choose a cell (F5) and apply the following formula-
=SUM(C5*$C$13,D5*$D$13,E5*$E$13)/SUM($C$13:$E$13)

Use SUM Function to Calculate Weighted Average with Percentages in Excel

  • Then press Enter to get the output.
  • After that, drag the “fill handle” down to fill all the cells.

  • In conclusion, we have calculated weighted average with percentages for each students.

Use SUM Function to Calculate Weighted Average with Percentages in Excel

Read More: Assigning Weights to Variables in Excel (3 Useful Examples)


2. Utilize SUMPRODUCT Function to Calculate Weighted Average with Percentages

If you want you can also use the SUMPRODUCT function to determine the weighted average too.

2.1 Calculate Weighted Average for Single Data

In this sub-method I have calculated the weighted average with percentage for single data.

Steps:

  • First, choose a cell (D12) and apply the formula-
=SUMPRODUCT(C5:C10,D5:D10)/SUM(D5:D10)

Utilize SUMPRODUCT Function to Calculate Weighted Average with Percentages in Excel

  • Simply, hit the Enter button.
  • In summary we have the desired output in our hands.

Utilize SUMPRODUCT Function to Calculate Weighted Average with Percentages in Excel

2.2 Calculate Weighted Average with Multiple Data

Here we will calculate for multiple data. Follow the steps properly-

Steps:

  • Select a cell (F5) and put the formula down-
=SUMPRODUCT(C5:E5,$C$13:$E$13)/SUM($C$13:$E$13)

Utilize SUMPRODUCT Function to Calculate Weighted Average with Percentages in Excel

  • Press Enter and then drag down the “fill handle” to fill all the cells.
  • Finally, we have calculated weighted average with percentage for multiple terms in excel.

Read More: Calculate Conditional Weighted Average with Multiple Conditions in Excel


Conclusion

As you can see, you can calculate the weighted average in Excel with percentages using two formulas. You can use any of the two formulas which will give you the same result. This calculation might be useful for calculating grades and numbers for the students. Also, you can apply these in many statistical analyses.


Related Articles

Siam Hasan Khan

Siam Hasan Khan

Hello! Welcome to my Profile. Here I will be posting articles related to Microsoft Excel. I have completed my BSc in Electrical and Electronic Engineering from American International University-Bangladesh. I am a diligent, goal-oriented engineer with an immense thirst for knowledge and an attitude to grow continuously. Continuous improvement and life-long learning are my mottoes.

1 Comment
  1. Thank you so much this was insightful looking forward to more articles and info

    Much appreciates

Leave a reply

ExcelDemy
Logo