To see the actual values behind an array, select it in the formula bar and press the F9 key. Supposing that the values to average are in cells B2:B6 and weights in cells C2:C6, our Sumproduct Weighted Average formula takes the following shape:
=SUMPRODUCT( values_range, weights_range) / SUM( weights_range) So, instead of multiplying each value by its weight individually, you supply two arrays in the SUMPRODUCT formula (in this context, an array is a continuous range of cells), and then divide the result by the sum of weights: Finding weighted average with SUMPRODUCT functionĮxcel's SUMPRODUCT function fits perfectly for this task since it is designed to sum products, which is exactly what we need. In this case, you'd better utilize the SUMPRODUCT function as demonstrated in the next example. Notice the difference between the normal average returned by the AVERAGE function (C8) and weighted average (C9).Īlthough the SUM formula is very straightforward and easy to understand, it is not a viable option if you have a large number of elements to average. In essence, it performs the same calculation as described above, except that you supply cell references instead of numbers.Īs you can see in the screenshot, the formula returns exactly the same result as the calculation we did a moment ago. If you have basic knowledge of the Excel SUM function, the below formula will hardly require any explanation:
Calculating weighted average by using the SUM function In Microsoft Excel, weighted average is calculated using the same approach but with far less effort because Excel functions will do most of the work for you.
In this example, in order to calculate the weighted average (overall grade), you multiply each grade by the corresponding percentage (converted to a decimal), add up the 5 products together, and divide that number by the sum of 5 weights: In mathematics and statistics, you calculate weighted average by multiplying each value in the set by its weight, then you add up the products and divide the products' sum by the sum of all weights. However, we want the average formula to consider the weight of each activity listed in column C. A usual average is easily calculated with the Excel AVERAGE function. Students' grades are often calculated using a weighted average, as shown in the following screenshot. In other words, each value to be averaged is assigned a certain weight. Weighted average is a kind of arithmetic mean in which some elements of the data set carry more importance than others.
#Multiple subtotals in excel by rate how to#
How to calculate weighted average in Excel.But what if some of the values have more "weight" than others and consequently contribute more to the final average? In such situations, you'll need to calculate the weighted average.Īlthough Microsoft Excel doesn't provide a special weighted average function, it does have a couple of other functions that will prove useful in your calculations, as demonstrated in the formula examples that follow. In one of the previous articles, we discussed three essential functions for calculating average in Excel, which are very straightforward and easy-to-use.
#Multiple subtotals in excel by rate professional#
This Microsoft Excel course includes all three Levels from Beginner to AdvancedĪs your instructor I will use my 18+ years of Excel training and certifications including: Microsoft Office Specialist, Expert and Master Instructor: 2000, XP (2002), 2003, 2007, 2010, 20, Microsoft Certified Trainer (MCT), Microsoft Certified Professional (MCP) to guide you step by step through Beginner, Intermediate and Advanced Levels of Excel.Īt completion of this course you will master Excel skills that will alleviate the discomfort of uncertainty and reduce time working through your daily tasks.The tutorial demonstrates two easy ways to calculate weighted average in Excel - by using the SUM or SUMPRODUCT function.
0 kommentar(er)
