Calculating a weighted average with percentages in Excel involves multiplying each value by its respective weight (percentage), summing up these weighted values, and then dividing by the sum of the weights.
In this Excel tutorial, you will learn how to calculate weighted average with percentages in Excel.
Sometimes you may want to put extra significance to some data points while calculating an average. Traditional arithmetic averages can’t do that as all data points carry equal significance. In such cases, you have to determine a weighted average that considers the varying significance or weight of each data point.
While calculating a weighted average, the weights of each data point are often expressed in percentages. For example, in the following image, we have varying weights for each data point and these weights are expressed in percentages. We will demonstrate formulas to calculate the weighted average here.
You can use an arithmetic formula, the SUM function, or a combination of the SUMPRODUCT and SUM functions to calculate the weighted average.
Note: We used Excel for Microsoft 365 to prepare this article. However, you can find the functions used in this tutorial in Excel 2021, Excel 2019, Excel 2016, Excel 2013, Excel 2010, and Excel 2007 versions as well.
What Is Weighted Average?
The weighted average refers to an average where different values in a dataset are given different levels of importance or “weights.”
Each number in a weighted average is multiplied by the corresponding weight (often expressed as a percentage or decimal), and the resulting products are then added together. The weighted average is then calculated by dividing the sum of the products by the sum of the weights.
You can use the following formula to calculate a weighted average.
Here,
W = weighted average
n = number of terms to be averaged
xi = data points to be averaged
wi = weights applied to corresponding xi values
How to Calculate Weighted Average in Excel?
We can apply the formula mentioned in the previous section to calculate a weighted average in Excel. Consider the following dataset. We have a few values and their weights. We have taken weights from 1 to 10 scale, but they can be any.
In cell C12, type in the following formula.
=(B5*C5+B6*C6+B7*C7+B8*C8+B9*C9+B10*C10)/(C5+C6+C7+C8+C9+C10)
Here, we have multiplied each value with the corresponding weight, summed the products, and divided the result with the sum of the weights.
After entering the formula, press the Enter key. You will get the weighted average value.
Now, let’s see how to calculate the weighted average when values or weights are expressed as percentages in Excel.
1. Calculating Weighted Average with Percentages Using Arithmetic Formula in Excel
Consider the following dataset where we have a student score out of 100 in Quiz, Assignment, Project, Mid Term Exam, and Term Final Exam. All these items carry different weights (expressed in percentages). We will use the arithmetic formula to calculate the weighted average score of this student here.
Steps:
- Insert the following formula in cell C12.
=(C6*E6+C7*E7+C8*E8+C9*E9+C10*E10)/(E6+E7+E8+E9+E10)
- Afterward, press the Enter key. You will get the required weighted average score.
2. Applying SUM Function to Calculate Weighted Average with Percentages in Excel
Instead of summing the product of values and corresponding weights using the arithmetic addition (+) operator, we can use the SUM function while calculating the weighted average.
Steps:
- Apply the following formula in cell C12.
=SUM(C6*E6+C7*E7+C8*E8+C9*E9+C10*E10)/SUM(E6:E10)
- Then, press the Enter key. You will get the same result as the previous method.
Note: If the weights add up to 100% i.e. 1, you can skip dividing with the sum of weights. In our dataset, the weight adds up to 100% (15% + 10% + 20% + 25% + 30% = 100%). So the following formula will return the same result as the previous two methods.
=SUM(C6*E6+C7*E7+C8*E8+C9*E9+C10*E10)
3. Using SUMPRODUCT and SUM Functions to Calculate Weighted Average With Percentages in Excel
In the previous two methods, we manually selected the cells of each value and corresponding weights. In large datasets, this can become tedious. We can use the SUMPRODUCT function as a quick alternative.
Steps:
- Insert the following formula in cell C12.
=SUMPRODUCT(C6:C10,E6:E10)/SUM(E6:E10)
- Afterward, press the Enter key. You will get the same weighted average score as the previous two methods.
4. Applying SUMPRODUCT and SUM Functions to Calculate Weighted Average With Percentages (When Base is Different)
In previous methods, we measured the scores of each item out of 100. But the base can be different as well.
Consider the following dataset. The scores of each item are measured out of different values. To calculate the weighted average in this case, we require an additional column to calculate the percentage of score for each score. This will make the base of each score equal.
Apply the steps below to calculate the weighted average in such situations.
Steps:
- First, insert the following formula in cell E6.
=(C6/D6)*100
- Then press the Enter key. You will get the percentage score for the first item.
- Now, hover your mouse pointer over the bottom right corner of cell E6. You will find the Fill Handle icon.
- Double-click on the Fill Handle icon to obtain the percentage score for all items.
- Now our dataset is ready for calculating the weighted average. Apply the following formula in cell C12.
=SUMPRODUCT(E6:E10,F6:F10)/SUM(F6:F10)
- Finally, press the Enter key and you will get the required weighted average score.
Read More: Assigning Weights to Variables in Excel
5. Calculate Weighted Average When Values Are Expressed As Percentages and Weights Are Expressed As Numbers in Excel
In the previous methods, the values were expressed as numbers and the weights were expressed as percentages. But it can be the opposite as well. Consider the following dataset. The scores are in percentages and the weights are in numbers here.
We can use the combination of the SUMPRODUCT and SUM functions here as well. Follow the steps below.
Steps:
- Insert the following formula in cell C13.
=SUMPRODUCT(C6:C11,D6:D11)/SUM(D6:D11)
- Then press the Enter key. You will get the weighted average score.
Calculate Weighted Average with Percentages in Excel for Real World Scenarios
We often require calculating the weighted average with percentages in real-world scenarios. Financial Investment Analysis, Employee Performance Appraisals, Customer Satisfaction Surveys, Calculating Stock Market Indices, Grading Systems, etc. are some common examples of where we need to calculate weighted averages in Excel.
In this section, we will discuss 2 of these real-world scenarios: Financial Analysis and Employee Performance Appraisals.
Example 1: Financial Analysis
In Financial Investment Analysis, the investors check attributes like Revenue, Net Profit, Assets, Debts, Equities, etc. of a company. But all these attributes may not have equal significance to the investors.
Consider the following dataset. We have Revenue, Net Profit, Asset Value, Debt, and Equity values of a few companies. We also have the weight percentages (i.e. significance) of each attribute.
Here, the company with the highest weighted average score will be more favorable for investment. Apply the steps below to calculate the weighted average score.
Steps:
- In cell H6, apply the following formula.
=SUMPRODUCT(C6:G6,TRANSPOSE($K$6:$K$10))/SUM($K$6:$K$10)
Since the attribute values are in a row and weights are in a column, we have used the TRANSPOSE function here to take the weights in a row as well.
- Then, press the Enter key. You will get the weighted average score for the first company.
- Finally, use the Fill Handle feature. You will get the weighted average scores of all items.
Read More: How to Calculate Weighted Average Price in Excel
Example 2: Employee Performance Appraisal
Companies have to evaluate employee performance periodically. An employee’s performance depends on attributes like Productivity, Teamwork, Leadership, Communication, etc. However, all these attributes may not have equal importance to the company.
Consider the following dataset. We have listed the Productivity, Teamwork, Leadership, and communication scores (out of 100) of a few employees. We also have the weight percentages (i.e. importance) of these attributes.
Apply the following steps to determine which employee has the highest weighted average score (i.e. performance).
Steps:
- Insert the following formula in cell G6.
=SUMPRODUCT(C6:F6,TRANSPOSE($J$6:$J$9))/SUM($J$6:$J$9)
- After that, press the Enter key. You will get the weighted average score for the first employee.
- Finally, drag the Fill Handle tool to obtain the weighted average score for all employees.
Download Practice Workbook
This concludes our tutorial on how to calculate weighted average in Excel with percentages. We used an arithmetic formula, the SUM function, and a combination of the SUMPRODUCT & SUM functions to calculate weighted average with percentages in Excel. This article also shows two examples of calculating the weighted average with percentages in Excel for real-life scenarios. We hope this article was helpful to you. Let us know your feedback in the comment section.
Related Articles
- How to Calculate Weighted Moving Average in Excel
- Calculate Conditional Weighted Average with Multiple Conditions in Excel
<< Go Back to Weighted Average Excel | How to Calculate Average in Excel | How to Calculate in Excel | Learn Excel
Thank you so much this was insightful looking forward to more articles and info
Much appreciates