
Aug 14, 2015
Calculating an average in Excel is really easy, but sometimes the business requirement calls for a more sophisticated weighted average. For example when analysing a company's results for investment purposes we could just take an average of the earnings over the last five years, but that would yield a result that would give equal importance to the older results. Ideally we would like to give more weight to the most current results, this is called a weighted average.
We will use the example above to demonstrate how to calculate a weighted average in Excel:
Step 1
List the company's net income figures for the last five years, starting with the current earnings. (The data below can be pasted into cell A1 of an excel spread sheet if you would like to try this example yourself.)
Fiscal Year | Net Income |
2014 | $23,279,315.00 |
2013 | $21,960,041.00 |
2012 | $21,265,234.00 |
2011 | $16,835,099.00 |
2010 | $11,394,732.00 |
Step 2
Apply a weighting to each data entry, so next to the income we will give each year a weighting according to how recent it is. As you can see the most recent data has a weighting of 5, while the oldest entry only has a weighting of 1.
Step 3
Use SUMPRODUCT to multiply each net income value by its weighting and then add all the products together.
- In the cell where the weighted average is to go type in =SUMPRODUCT(
- Next, select the values you wish to average, in this case B4:C8
- Type “,”
- Select the weightings, in this case C4:C8
- Close the ) and press Enter
The finished formula should read: =SUMPRODUCT(B4:B8,C4:C8) and gives exactly the same result as the following: (B4*C4)+(B5*C5)+(B6*C6)+(B7*C7)+(B8*C8) but is much quicker and easier to enter , particularly if you have a lot of values.
Step 4
Divide the total weighted earnings by the total weighting to find the weighted-average. Double click back in your formula with the SUMPRODUCT and at the end add: /SUM(C4:C8) and press enter.
Our final weighted average is $20,873,158.07.
For more information, take a look at New Horizons' Excel Training Courses.
How do your Excel skills stack up?
Test NowNext up:
- Exchange Online Protection and Evolution
- Create a Custom Tracking View in Project 2013
- 5-steps to conducting quality feedback sessions
- Automate a table of contents in InDesign
- Implementing and administering AD RMS
- Multiple Desktops in Windows 10
- Easily sample and transfer colours between objects in InDesign
- Guidelines for Effective Time Management
- Rounding Numbers in Excel, Part 1
- Show Excel Chart in SharePoint 2013
Previously
- Remote Events in SharePoint 2013
- How do you honk?
- Spellcheck and custom dictionaries in Microsoft Word
- The grid system of Bootstrap
- Dude, Where’s My Ruler?
- “Hey, Cortana”
- When it’s not harassment
- The Many Flavours of Windows 10
- Easily Create Complex Illustrations with Basic Tools and Pathfinder Commands in Adobe Illustrator
- Excel – Fill in the Blanks