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.
In the following, I have shared 2 simple methods to calculate weighted average with percentages in excel. Stay tuned!
1. Using SUM Function to Calculate Weighted Average in Excel 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-
- Starting with, select a cell (D12) to apply the formula.
- Write the formula down-
- Therefore press Enter.
- Finally, we have successfully calculated the weighted average with percentages using a simple formula.
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-
- In the same fashion choose a cell (F5) and apply the following formula-
- 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.
2. Combining Excel SUM and SUMPRODUCT Functions 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.
- First, choose a cell (D12) and apply the formula-
- Simply, hit the Enter button.
- In summary we have the desired output in our hands.
2.2 Calculate Weighted Average with Multiple Data
Here we will calculate for multiple data. Follow the steps properly-
- Select a cell (F5) and put the formula down-
- 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.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
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.