Calculate Weighted Average

Excel Tip #024

Calculate Weighted Average

Excel makes it extremely easy to calculate the average of data by using the AVERAGE function. But what if some of the values have more "weight" than others? For those cases, you'll need to calculate the Weighted Average.

Although Excel doesn't have a weighted average function, it does have a function that does most of the work for you: SUMPRODUCT.

In order to calculate the weighted average, we are having 2 columns of data. The first column contains the grades for each assignment and the second column contains the weights. A higher weight will cause the data (assignment) to have a greater effect on the final grade. The weights can also be greater than 100% but it would be meaningful if it is 100%

Below is the example data containing 5 assignments along with their marks and the weights.
Example - Weighted Average
Before going to the example, let us learn about SUMPRODUCT. This function multiplies the corresponding items in the arrays and returns the sum of the results.

Syntax:
     SUMPRODUCT(array1, [array2], [... array n])
where array1 is mandatory and the rest of the array are optional.

We are going to use SUM function along with SUMPRODUCT to calculate the weighted average. In our example,
     B2:B6 - Range of cells that contains the marks
     C2:C6 - Range of cells that contains the weights

The formula is,
     =SUMPRODUCT(B2:B6,C2:C6)/SUM(C2:C6)

We are defining the marks as Array1 and their corresponding weights as Array2. Finally the resultant is divided with the sum of the weights to get the Weighted Average.

Calculating Weighted Average
SUMPRODUCT is multiplying (finding the product of) each assignment's marks times its weight, then adding all of the products together. In other words, it finds the sum of the products, which is where it gets its name. So for Assignment 1 it multiplies 85 by 20, and for Assignment 1 it multiplies 92 by 15 etc.

The way the SUMPRODUCT function calculates is

=(B2*C2)+(B3*C3)+(B4*C4)+(B5*C5)+(B6*C6)

By itself, SUMPRODUCT will give us a huge number 8,925. This is where the second part of our formula comes in: /SUM(C2:C6). This part brings the value back down to a normal grade range, making the answer 89.25.

Remember that the weights don't need to add up to 100%, SUM part will take care of that.

Comments

Post a Comment

Popular posts from this blog

VLOOKUP - Exact Match & Approximate Match

Image
Excel Tip #013 VLOOKUP - Exact Match & Approximate Match In the previous tip #012 (VLOOKUP & INDEX + MATCH), we learnt about VLOOKUP, it's syntax and how to use it. We also learnt about its drawback and how INDEX & MATCH can be helpful. Like said in the previous tip, there are 2 match option available in VLOOKUP - Exact & Approximate Match . Let us first refresh the syntax of VLOOKUP. Syntax: = VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup]) In simple terms, =  VLOOKUP (value/data that is used as reference, data table, the column # where our resultant data resides, exact (or) approximate match) Let's have a closer look at the last argument you supply to the Excel VLOOKUP function - range_lookup. You are likely to get different results in the same formula depending on whether you enter TRUE or FALSE (Approximate or Exact). If range_lookup is set to FALSE , the formula searches for exact match, i.e. for the lookup value exac
Read more

Row 1 Is Missing. How to unhide?

Image
Excel Tip #030 Row 1 Is Missing. How to unhide? As you know, hiding and unhiding rows in Excel is simple as selecting them, right-clicking and choosing Hide or Unhide . The trick to selecting hidden rows, is to select a row above or below and then drag to included the hidden rows. For example, if you accidentally hide row 1 of your worksheet or inherit someone else's worksheet with one or more of the top rows hidden, you may be wondering how to unhide them since apparently you cannot select them. Row 1 Hidden There is a really easy solution. 1) Point to the row 2 header , press the left mouse button and drag upward to the top of the worksheet. Although you cannot see it, row 2 and row 1 are now selected 2) Right click anywhere on row 2 3) Choose Unhide from the right-click menu Unhiding hidden Row 1 NOTE : This also works for multiple hidden rows. There is another method of doing the same if Row 1 is hidden. 1) Go to the top left corner of the sheet abov
Read more

Search Backwards in Excel

Excel Tip #022 Search Backwards in Excel Sometimes we take some things for granted in Excel and assume that everyone else knows too. Here's a tip which lot of Excel users don't know about it. Many Excel users know that the quickest way to find something in a large worksheet is to use Excel's Find command. On the Home tab, in the Editing group, click Find & Select , then Find or, preferrably, use the keyboard shortcut CTRL+F instead. Each time you click Find Next you will be taken to the next matching cell, starting from the active cell and going down the sheet until you finally come to the last one. But what if you want to find the 'last' matching value in a sheet and there are dozens or even hundreds of matching values? You don't want to click through each one until you finally reach the end. Here's a simple trick that will save you some time. Select a cell below the range you want to search ( CTRL+END is a shortcut you can use to quick
Read more