Weighted Average Excel: Easy Tips For You

Udemy Editor

Weighted Average is one of the most straightforward and reliable means of calculating an average of several numerical values while taking into account possible variations. Calculating with Excel makes it even easier because Excel has provided an environment for you to derive the weighted average by simply using formulas. Of course, you may feel that Excel is a bit of a hard nut to crack but then, I would like you to know that is not quite true. You just need someone to hold you by the hand and shine the light on a few, seemingly dark corners. If you want to learn how to use Microsoft Excel fast, I recommend this approach.

In this post, I will walk you through the steps used in calculating weighted averages in Excel and I will do so by using the experiences of two individuals: Brad and Sue. By looking at things from their respective points of view, you will find it easier to understand the basics and calculations of weighted averages and hopefully, at the end agree with me that it is simple after all.

Weighted averages may actually sound like one of those mathematical terms that you prefer to avoid rather than tackle especially if you hate maths. Some people may even regard it as a no go area especially when it has to do with a formula-based system like Excel.

Actually, it is not difficult…

Really, calculating weighted averages is something anyone can grasp easily. Come to think of it, having read up to this point, I am quite sure that you are smart enough to understand how to calculate weighted averages even when the scenarios are different! So, you have my word that by the end of this article, you will know how to calculate weighted averages using Excel like a pro.

Let’s start with the basics

In order to understand how to calculate weighted averages in Excel, I prefer to begin from scratch or as close to scratch as possible. So, let’s take a look at the term ‘averages’ before proceeding to its ‘weighted’ version.

Averaging is in essence, a very functional method of summarizing data and it is relevant in various aspects of life: business, education and even government. Calculating averages is quite straightforward as you only have to sum all the values and divide by the number of values.

Two scenarios…

To help us better relate with weighted averages and drive our points home, we will work with two people; Brad and Sue. Now, Brad is a High School student while Sue is a staff of a manufacturing company.

Brad has to hit a minimum percentage in order to get a pass mark in school. The teacher has decided to ensure that the students exercise proper effort in certain academic activities and to achieve this, she attaches more points to those activities than she does to others. In other words, she places more weight on those activities. Thus, in order to compel Brad to devote appropriate effort to those desired areas, the teacher adopts a weighted average grading policy.

Ok, so we will take a look at what the grading policy would have looked like if it was unweighted (that is regular averaging) and then what it looks like going by the weighted average grading policy now adopted.

Unweighted Grading Policy:

2 Tests…………………………………………25% Each Test…………………….12.5%

2 Quizzes……………………………………..25% Each Quiz……………………12.5%

3 Homework assignments…………….37.5% Each Assignment………….12.5%

Class participation……………………….12.5% Participation…………………12.5%

TOTAL: 100%

Weighted Grading Policy:

2 Tests…………………………………………40% Each Test…………………….20%

2 Quizzes……………………………………..20% Each Quiz……………………10%

3 Homework assignments………………30% Each Assignment………….10%

Class participation……………………….10% Participation…………………10%

TOTAL: 100%

You can see from the tables above that when the grading policy was unweighted, each academic activity had the same score of 12.5%. All the teacher did was to multiply that figure by the number of times the activity was executed. However, in the second table where the policy was weighted, the test score had a higher percentage than the rest. This means that each test ‘weighed more’ and was worth twice the other academic activities in terms of scores. In summary, Brad would have to read harder to score high in his tests and improve his overall score.

Sue’s company on the other hand, manufactures widgets and from time to time, Sue has to calculate the average price at which widgets were sold. Simply calculating the regular average will not suffice in this case because it does not account for the trends and seasonal variations. Now, what she has to do is provide statistics that indicate the rise and fall in widget prices each month or within the period being reviewed. Let’s look at what Sue’s books look like with weighted and unweighted averages.

Widget Sales/Prices

	APR	MAY	JUN	JUL	AUG	SEP	OCT	NOV	DEC	JAN	FEB	MAR
Widgets Sold	45	43	60	45	50	68	73	66	80	105	110	120
Price	1.4	1.3	1.8	1.4	1.5	2.1	2.3	2	2.6	3.1	3.2	3.5

In the table above, the unweighted average of the Widget price for the months listed would be 2.2. This was derived by summing up all the prices from April to March and then divided by 12, being the number of months. As mentioned earlier, this figure will be quite misleading.

Therefore, Sue’s calculation must take the number of widgets sold as well as the price fluctuations into consideration to provide a weighted average that represents the actual average price of widgets sold within those months. To do this, she will enter the data in the table above in a particular manner into an Excel table then calculate the weighted average using some very easy formulas.

Let’s go with Smart Sue….

So, we will go with Sue from here on and follow her smart steps. Here goes…

i. First thing she does is to open an Excel sheet and save it with a file name

ii. Then she enters the values in the table above (that’s pretty straightforward stuff!)

iii. Now, she clicks the cell where she wants the result of the weighted average value to appear, then clicks the formula box where she will enter the formula to be executed by Excel.

iv. Pay close attention; there is no formula preset in Excel that expressly calculates weighted averages thus Excel users sometimes have to be creative especially when using excel for business calculations. So what Sue does is to create a formula using the SUMPRODUCT function. What this function does is to calculate the values in two or more lists of numbers and return the sum of product of corresponding values.

v. Now, because Sue’s table does not attach any weights to various values, she she has to modify the formula to calculate using the values she has provided in the table.

vi. In the formula box she clicked above, she enters the following formula:

=SUMPRODUCT(B3:M3,B2:M2)/SUM(B2:M2). After entering the formula, she presses the ‘enter’ button on the keyboard to execute the formula. Excel returns 2.4 as the Weighted Average of Widget prices for the months of April to March.