How to calculate the weighted average in Excel
Posted on: 03/13/2017
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.
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 certified Microsoft Trainer (MCT) and works as a full-time Trainer at BakerHostetler - one of the largest law firms in the US. He runs a YouTube channel with 900+ technology videos that cover various tools such as Excel, Word, Zoom, Teams, Gmail, Google Calendar, and Outlook. To date, the channel has helped over 20 million viewers.
Menard also does 2 to 3 public speaking events every year, presenting at the Administrative Professional Conference (APC), the EA Ignite Conference, the Support Staff Conference, the University of Georgia, and CPA conferences. You can connect with him on LinkedIn at https://chrismenardtraining.com/linkedin or watch his videos on YouTube at https://chrismenardtraining.com/youtube.
Categories