WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Weighted Average in Excel

Weighted Average in Excel

By Madhuri ThakurMadhuri Thakur | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

Weighted Average in excel is calculated when we assign each data set with some kind of weights like weightage, in statistics or portfolios we use weighted average to calculate more robust and impact observations and calculations, the formula for weighted average is very simple which is = (w1x1+w2x2+….+wnxn)/(w1+w2+..wn) where w is the weight assigned to the x value and we use sumproduct function to calculate the weighted average.

Weighted Average in Excel Calculation

Weighted Average is an average in which each observation in the data set is assigned or multiplied by weight before summing to a single average value.

  • In this procedure, each quantity to be averaged is assigned a weight that determines the relative importance of each quantity. Weightings are the equivalent of having that many like items with the same value involved in the average.
  • It is a more accurate measurement of scores, grades, or investments that are of relative importance to each other. This is often the case with investment portfolios, grade scoring, and other statistics.

Explanation Of Weighted Average in Excel

In excel weighted Average, every value is assigned a specific weight. For example, in example 3, the employee’s performance is most affected by his/her productivity as it has the most weight rather than the attendance, which has the least weightage.

For knowledge purposes, let us alter the values, in example 3, and see how the weighted average affects. Exchange the values of productivity and attendance with each other to see how the weighted average changes.

Weighted Average Explanation Eg

The value of the weighted average changed from 81 to 86 because productivity has the most weight.

Similarly, Attendance has less weight, so the effect on the average is not so much.

The formula for Weighted Average in Excel

Formula of Weighted Average

Where ‘w’ stands for ‘Relative weight’ in % &,

‘x’ stands for ‘value.’

The above-depicted weighted average formula is a basic mathematical formula in which the excel weighted average is calculated.

In Excel, we use two functions to calculate the weighted average in excel. They are:

  1. SUMPRODUCT() function &
  2. SUM () functions.

Steps to Calculate Weighted Average in Excel

There are few steps to calculate the weighted average in excel. Please find below the steps to calculate the weighted average in excel.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course
You can download this Weighted Average Excel Template here – Weighted Average Excel Template

Step 1) Draw a table with two columns with one column contains values & the other contains their weights.

Weighted Average step 1

Step 2) Calculate the SUMPRODUCT, i.e., multiply each value by its weight and then calculate their sum in order to get the sum-product.

Weighted Average step 2

Step 3) Calculate the sum of the weights in another cell.

Weighted Average step 3

Step 4) As the formula for calculating the weighted average suggests, now Divide the sum-product with the sum of weights.

Weighted Average step 4

Step 5) And the resulting value will be the weighted average.

Weighted Average step 5

Weighted Average Excel Examples

Example #1

First, let us start with the basic example; we have values in the A column and weights in the B column. And we need to calculate the weighted average in excel. Let us start mathematically first.

Weighted Average Example 1

#1 – In order to calculate the weighted average in excel, In any cell, write the formula,

Weighted Average Example 1-1

#2 – Press Enter, and we have our weighted average.

Weighted Average Example 1-2

Example #2

In a class where a student has his marks for different subjects, and he/she has different weightage for each subject. The teacher needs to calculate the weighted average in excel for each subject.

Weighted Average Example 2

#1 – In Excel to calculate the weighted average in excel, first, we need to calculate a SUMPRODUCT, write down the following formula,

Weighted Average Example 2-1

#2 – Now calculate the total number of values.

Weighted Average Example 2-2

#3 – Now, we can calculate the weighted average by dividing the SSUMPRODUCT with the SUM of the weightage assigned.

Weighted Average Example 2-3

#4 – Press Enter, and it returns the weighted average.

Weighted Average Example 2-4

Example #3

Suppose for an employee. The performance is defined by few weightage as Productivity has the most weightage than compared to accuracy, and accuracy has more weightage compared to attendance and so on.

Weighted Average Example 3

#1 – As discussed, first calculate SUMPRODUCT, write down the formula,

Weighted Average Example 3-1

#2 – Now we calculate the sum of the weightage,

Weighted Average Example 3-2

#3 – To calculate the weighted average, divide the SUMPRODUCT with the sum in excel of the weightage,

Weighted Average Example 3-3

#4 – Press Enter, and we have a weighted average for the employee.

Weighted Average Example 3-4

Things to Remember about Weighted Average formula in Excel

  1. SUMPRODUCT should be the sum of the products of the respective values and weights.
  2. We need to calculate the sum of the weights.
  3. The higher the weight, the more the effect will be on the value of the weighted average.

Recommended Articles

This has been a guide to Weighted Average in Excel. Here we discuss how to calculate Weighted Average using Excel Formulas (SUM and SUMPRODUCT) along with excel example and downloadable excel templates. You may also look at these useful functions in excel –

  • Average Formula
  • How to Calculate Age in Excel?
  • AVERAGE Function in Excel
  • WACC Formula
8 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Download Coursera IPO Financial Model

By continuing above step, you agree to our Terms of Use and Privacy Policy.
WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download Weighted Average Excel Template

Coursera IPO Financial Model & Valuation Free Download