How to calculate the weighted average in Excel

Posted on:  03/13/2017
How to calculate the weighted average in Excel

Need to calculate the weighted average for work or school? There are two ways to do it but the easiest is to use the sumproduct function in Excel. It is easy to calculate the average. Just use the average function in MS Excel which will show you the calculated “central” value of a set of numbers.

What do you do when some values have more weight than others? An example would be if we shipped 140 units of Product A that cost $5 each, and 10 units of product B that cost $125 each. The average cost is $130 / 2 = $65, but the weighted average cost is $13. Who uses the weighted average? A lot of teachers and professors use a weighted average for their classes. Some assignments are not weighed as much as a test, and some tests aren’t weighted as much as the final exam.

Weighted average

Sumproduct Function

With the sumproduct function, you are multiplying all the grades times their respective weight and totaling them, then dividing by the total weight to get the weighted average. So the image on the left would be =B4*C4, B5*C5, B6*C6, etc…Total those numbers and divide by the total of the weight.

The Microsoft Excel SUMPRODUCT function multiplies the corresponding items in the arrays and returns the sum of the results.The SUMPRODUCT function is a built-in function in Excel that is categorized as a Math/Trig Function. It can be used as a worksheet function (WS) in Excel. As a worksheet function, the SUMPRODUCT function can be entered as part of a formula in a cell of a worksheet.

Video for weighted average

Excel file for weighted average

Feel free to download the weighted average Excel file  chris_menard_weighted_average

 

Microsoft Office Sumproduct webpage

https://support.office.com/en-us/article/SUMPRODUCT-function-16753e75-9f68-4874-94ac-4d2145a2fd2e

Chris Menard

Chris Menard is a Senior Training Specialist at SurePoint Technologies. Chris is certified in Excel, Word, PowerPoint, and Outlook. Menard has a YouTube channel with other 600 technology videos covering Excel, Word, Zoom, Teams, Outlook, Gmail, Google Calendar, and other resources that over 7 million viewers have very appreciated. Because of Chris's certification and expertise with Microsoft, Chris is a proud member of Microsoft's Creator Team. Being a member of Microsoft's Creator Teams means many of his videos are available on Microsoft 365 YouTube channel and Microsoft support websites.

Categories