Weighted Average in Excel

 

 

Calculate Weighted Average in Excel

 

 

 

Article by Jed Jones

 

Microsoft Excel allows you to perform a number of powerful calculations. The power of Excel enables businesspeople, scientists, students and researchers to perform virtually almost any needed mathematical analysis that involves algebra, geometry and statistics.

Many Excel calculations are performed by constructing a formula from scratch, while others leverage something called a function. A function can be thought of as a pre-configured formula that can transform numbers, text or both in desirable ways.

One common mathematical calculation you may need to perform in Excel is that of the weighted average. A weighted average is a way of averaging two or more numbers by treating some as more significant or important than others (i.e., by weighting them differently).

For example, to find the average price paid for a product that was sold in three different orders at different price points and with a different number of units per order, as follows:

Order 1: $20 x 200 units

Order 2: $40 x 350 units

Order 3: $45 x 150 units


If you simply average the prices ($20, $40, $45), you will not get the correct answer, since each order had a different number of units. Instead, you need to use a weighted average in order to properly "weight" each price point by the number of units sold to find out the average price paid.

Here are two methods to calculate the weighted average in Excel, using the numbers in the above-mentioned example:

1. The first method requires no knowledge of Excel functions, but it does require a bit of pre-calculation. You need to start by calculating the percentage contribution to the "weight" of each set of units sold. To do this, just add up the number of units sold in each order to get the total of number of units sold. Then, divide each units sold number by the total units sold. In this case, the total units sold is 700, so the resulting percentage contribution coefficients are: 0.285 (for the 200 units portion), 0.5 (for 350 units), and 0.214 (for 150 units).

Now, just multiply each percentage coefficient times its corresponding price, then add those products together. Here is how this formula looks (when doing this in Excel, substitute the cell names for the numbers below):

= ($20 * 0.285) + ($40 * 0.5) + ($45 * 0.214)

2. The second method does not require pre-calculation of the percentage contribution coefficients of each price, but it does require the combined use of two functions: SUMPRODUCT and SUM. Here is how the formula looks:

=SUMPRODUCT(H11:H13,I11:I13)/SUM(I11:I13)

(where H11, H12 and H13 contain the prices for each order and I11, I12, and I13 contain their respective number of units sold).

Both methods yield identical results, which in this case is: $35.36 (rounded to the second decimal).

Download a FREE Excel cheat sheet at: www.Untangle-Your-Thoughts.com

Grab a Copy of My Excel - Hands On Guide!

Microsoft Excel Guide

Microsoft Excel Functions And Formulas Guide

Discover more about using Functions and Formulas - CLICK HERE!

 

-----------------------------------------------------------------------------------------------------------

Trouble Free Computing?

PC Security Guide



CLICK HERE - For More Information Now!

-----------------------------------------------------------------------------------------------------------

>>> My FREE Windows Newsletter! >>>

 

Free-Report

Claim YOUR Fortnightly copy of my FREE Windows Newsletter covering:

Windows XP, Windows Vista, Windows 7 , Microsoft Office and Windows Live Services - Sign-up TODAY!!!

 
Name:
Email:
 

Your Details are secure - we never pass them on to anyone else!

Privacy Policy

Enjoy!

Kind Regards

Marc Liron

Marc Liron - Microsoft MVP
www.marcliron.com
www.twitter.com/marcliron
http://blog.marcliron.com

 

Links

Trouble Free Computing?

 

PC Security Guide

"Discover The Simple But Powerful Secrets To Keeping Out Viruses, Hackers, Trojans, Keyloggers And Many Other Online Security Threats"

Expert PC security advice from a long standing Microsoft MVP!

Get YOUR Copy NOW!

--------------------

 

 

Related Articles:

XLS Value Error

Spreadsheet Value Error

Excel If Value

Understanding Excel Dates and Times

Excel 2003 Vs Excel 2007 - What's New in Excel 2007

Calculate the Coefficient of Variation in Excel

5 Excel Mortgage Amortization Formulas

How to Wipe and Clean Excel Spreadsheets Thoroughly