How to Calculate Weighted Average with Percentages in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.

Overview of How to Calculate Weighted Average with Percentages in Excel

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.

Mathematical Formula to Calculate 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.

Dataset for Calculating Weighted Average in Excel

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)

Using Arithmetic Formula to Calculate Weighted Average in Excel

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.

Weighted Average Value Calculated with Arithmetic Formula

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.

Dataset for Calculating Weighted Average in Excel with Weights in Percentages

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.

Using Arithmetic Formula to Calculate Weighted Average in Excel with Weights in Percentages


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.

Applying SUM Function to Calculate Weighted Average in Excel with Weights in Percentages

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)

Calculating Weighted Average When Weight Percentages Add Up to 100%


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.

Combining SUMPRODUCT and SUM Functions to Calculate Weighted Average with Weights in Percentages in Excel


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.

Dataset for Calculating Weighted Average When Base of Values Is Different for Each Item

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.

Applying Formula to Convert the First Score to Percentage

  • Now, hover your mouse pointer over the bottom right corner of cell E6. You will find the Fill Handle icon.

Hovering Mouse Pointer to View the Fill Handle Icon

  • Double-click on the Fill Handle icon to obtain the percentage score for all items.

13. Using Fill Handle to Obtain Percentage of 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.

Applying SUMPRODUCT and SUM Functions to Get the Weighted Average with Percentages in Excel When Base Is Different

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.

Dataset for Calculating Weighted Average When Values Are in Percentages and Weights Are in Numbers

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.

Using SUMPRODUCT and SUM Functions to Calculate Weighted Average with Percentages in Excel


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.

Dataset for Calculating Weighted Average in Excel for Financial Investment Analysis

Click the image for a detailed view

Note: As lower debt is more favorable, we have taken negative values for debts.

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.
Using SUMPRODUCT, TRANSPOSE, and SUM Functions to Calculate Weighted Average with Percentages in Excel

Click the image for a detailed view

  • Finally, use the Fill Handle feature. You will get the weighted average scores of all items.
Final Output After Dragging Down the Fill Handle Icon

You can click the image for a detailed view

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.

Dataset for Calculating Weighted Average in Excel for Employee Performance Appraisal

Click the image for a detailed view

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.
Calculate Weighted Average with Percentage Using SUMPRODUCT, SUM and TRANSPOSE Functions in Excel

Click the image for a detailed view

  • Finally, drag the Fill Handle tool to obtain the weighted average score for all employees.
Weighted Average Score for All Employees After Using the Fill Handle Tool

You can click the image for a detailed view


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


<< Go Back to Weighted Average Excel | How to Calculate Average in Excel | How to Calculate in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
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

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo