Excel – Weighted Average Calculation

 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 Now  

About the Author:

Magdalena Todor  

With over 20 years experience as a facilitator and university lecturer, Magda is one of our most senior and experienced trainers. With previous practical on the job experience as a project manager she embodies a brilliant balance of training experience and business knowledge. With every event she delivers, Magda takes a wholehearted approach to ensure every course is impactful, relevant and a genuinely positive learning experience for all.

Read full bio
top