Calculate Weighted Average in Excel

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!