Excel Functions Tutorials
- Excel Tips
- Excel vs Access
- Excel Rows vs Columns
- Apple Numbers vs Excel
- 3D Reference in Excel
- Absolute Reference in Excel
- Mixed References in Excel
- Excel Reference to Another Sheet
- Array Formulas in Excel
- Arrays in Excel VBA
- Auto Numbering in Excel
- AutoFit in Excel
- AutoCorrect in Excel
- AutoSave in Excel
- AutoRecover in Excel
- Bullet Points in Excel
- Break Links in Excel
- Barcode in Excel
- Change Case in Excel
- CAGR Formula in Excel
- Calculate Age in Excel
- Calculate Percentage in Excel Formula
- Cell Reference in Excel
- Checklist in Excel
- Circular Reference in Excel
- Column Sort in Excel
- Column Lock in Excel
- Move Columns in Excel
- Custom List in Excel
- Consolidate in Excel
- Combine Cells in Excel
- Compare Two Columns in Excel
- Compare and Match Columns in Excel
- Compound Interest Formula in Excel
- Convert Columns to Rows in Excel
- Convert Date to Text in Excel
- Convert Numbers to Text in Excel
- Convert Text to Numbers in Excel
- Convert Excel to CSV
- Count Characters in Excel
- Count Rows in Excel
- Count Unique Values in Excel
- Countif not Blank in Excel
- Create Templates in Excel
- Family Tree in Excel Template
- Custom Number Format in Excel
- Delete Row Shortcut in Excel
- Divide in Excel Formula
- Drop Down List in Excel
- Dynamic Tables in Excel
- Dashboard in Excel
- KPI Dashboard in Excel
- Date to Text in Excel
- Date Format in Excel
- Database in Excel
- Delta Symbol in Excel
- $ Symbol in Excel
- Excel Column to Number
- Edit Drop-Down List in Excel
- Equations in Excel
- Exponents in Excel
- Excel Extensions
- Excel Translate
- Excel Not Responding
- Excel Find and Replace
- Find and Select in Excel
- Excel Subtraction Formula
- Excel Formula for Grade
- Excel as Calculator
- Excel Formula Not Working (Updating)
- Excel Table Styles & Formats
- Excel vs Google Sheets
- External Links in Excel
- Excel Alternate Row Color
- Excel Worksheet Tab
- Extract Number from String Excel
- Evaluate Formula in Excel
- Find Duplicates in Excel
- Finding Links in Excel
- Filter Shortcut in Excel
- Formatting in Excel
- Format Numbers to Millions & Thousands in Excel
- Format Phone Numbers in Excel
- Formula Errors in Excel
- Fractions in Excel
- Frequency Distribution in Excel
- Group in Excel
- Group Worksheets in Excel
- Group Columns in Excel
- Hide Formula in Excel
- Hiding a Column in Excel
- Highlight Every Other Row in Excel
- Highlight Duplicates in Excel
- How to Create a Formula in Excel?
- How to Create an Excel Spreadsheet?
- How to Add Text in Excel Formula?
- How to Create Dashboard in Excel?
- How to Copy Sheet in Excel?
- How to Delete Pivot Table?
- How to Calculate Percentage Increase in Excel?
- How to Multiply in Excel Formula?
- How to Unhide Columns in Excel?
- Insert Date in Excel
- Insert Calendar in Excel
- Import Data into Excel
- Insert Comment in Excel
- Insert Hyperlinks in Excel
- Insert Multiple Rows in Excel
- Insert Row Shortcut in Excel
- Insert New Worksheet in Excel
- Insert (Embed) an Object in Excel
- Insert Image in Excel Cell
- Insert Page Break in Excel
- Line Breaks in Excel
- Linear Interpolation in Excel
- Leading Zeros in Excel
- Last Day of the Month in Excel
- Logical Operators in Excel
- Lookup Table in Excel
- Mortgage Calculator in Excel
- Moving Average in Excel
- Not Equal to in Excel
- Numbering in Excel
- Name Manager in Excel
- Page Numbers in Excel
- Page Break in Excel
- Personal Budget Template in Excel
- Project Management Template in Excel
- Percentage Difference in Excel (Increase / Decrease)
- Pivot Table Calculated Field & Formula
- Pivot Table Sort
- Pivot Table From Multiple Sheets
- Print Comments in Excel
- Print Excel Gridlines
- Print in Excel
- Print Preview in Excel
- Print Area in Excel
- Print Titles in Excel
- Print Labels From Excel
- Project Timeline in Excel
- Protect Sheet in Excel
- Ratio in Excel Formula
- Random Numbers in Excel
- Randomize List in Excel
- Refresh Pivot Table in Excel
- Relative References in Excel
- Remove Blank Rows in Excel
- Remove Duplicates in Excel
- Remove Duplicates from Excel Column
- Remove Hyperlinks in Excel
- Remove Space in Excel
- Remove Leading Spaces in Excel
- Remove Watermark in Excel
- Row Limit in Excel
- Rows and Columns in Excel
- Rows to Columns in Excel
- Row Header in Excel
- Search in Excel
- Search For Text in Excel
- Share an Excel Workbook
- Shortcut to Merge Cells in Excel
- Show Formula in Excel
- Split Cells in Excel
- Separate Text in Excel
- Strikethrough in Excel
- Strikethrough Text in Excel
- Sum by Color in Excel
- Subscript in Excel
- Superscript in Excel
- Themes in Excel
- Timesheet Calculator in Excel
- Top 20 Keyboard Shortcuts in Excel
- Unmerge Cells in Excel
- Uppercase in Excel
- Word Count in Excel
- Word Cloud in Excel
- Watermark in Excel
- Weighted Average in Excel
- Wildcard in Excel
- Financial Functions in Excel (17+)
- Logical Functions in Excel (15+)
- TEXT Functions in Excel (29+)
- Lookup Reference in Excel (44+)
- Maths Functions in Excel (52+)
- Date and Time Function in Excel (22+)
- Statistical Function in Excel (50+)
- Information Functions in Excel (5+)
- Excel Charts (48+)
- Excel Tools (98+)
- VBA (162+)
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.
Table of Contents
- Weighted Average in Excel Calculation
- Explanation Of Weighted Average in Excel
- How to Calculate Weighted Average in Excel?
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 purpose let us alter the values in example 3 and see how the weighted average effects. Exchange the values of productivity and attendance with each other to see how the weighted average changes.
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.
Formula for Weighted Average in Excel
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:
- SUMPRODUCT() function &
- 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.
Step 1) Draw a table with two columns with one column contains values & the other contains their weights.
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.
Step 3) Calculate the sum of the weights in another cell.
Step 4) As the formula for calculating the weighted average suggests, now Divide the sum-product with the sum of weights.
Step 5) And the resulting value will be the weighted average.
Weighted Average Excel Examples
First, let us start with the basic example; we have values in A column and weights in B column. And we need to calculate the weighted average in excel. Let us start mathematically first.
#1 – In order to calculate the weighted average in excel, In any cell write the formula,
#2 – Press Enter and we have our weighted average.
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.
#1 – In Excel to calculate the weighted average in excel, first, we need to calculate a SUMPRODUCT, write down the following formula,
#2 – Now calculate the total number of values.
#3 – Now we can calculate the weighted average by dividing the SSUMPRODUCT with the SUM of the weightage assigned.
#4 – Press Enter and it returns the weighted average.
Suppose for an employee the performance is defined by few weightage like Productivity has the most weightage than compared to accuracy and accuracy has more weightage compared to attendance and so on.
#1 – As discussed, first calculate SUMPRODUCT, write down the formula,
#2 – Now we calculate the sum of the weightage,
#3 – To calculate the weighted average, divide the SUMPRODUCT with the sum of the weightage,
#4 – Press Enter and we have the weighted average for the employee.
Things to Remember about Weighted Average formula in Excel
- SUMPRODUCT should be the sum of the products of the respective values and weights.
- We need to calculate the sum of the weights.
- The higher the weight the more the effect will be on the value of weighted average.
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 –