## Weighted Average in Excel

*We Recommend:*Speed Up Windows XP by up to 70% (takes 3 min)

# 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 Functions And Formulas Guide**

**Discover more about using Functions and Formulas -
CLICK HERE**!

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

**Trouble Free Computing?**

**
CLICK
HERE
- For More Information Now!
**

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

**>>>
My FREE Windows Newsletter! >>>**

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**!!!

Enjoy!

**Kind Regards**

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

## Links

#### Trouble Free Computing?

"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!**

**--------------------**

**Related Articles:**

**
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**