![]() In this situation, we should first use SUMPRODUCT to multiply each grade by weight and add the results. In this example, we also want to calculate the final grade for the student, but the weights are expressed as numbers and their total is 15: However, what should we do, if the weights do not sum up to 100% or are not percentage? To calculate the weighted average in such situations, we should use a combination of two functions: SUM.PRODUCT and SUM. The example described above was quite simple, as the weights were percentage values and they summed up to 100%. The following picture shows that an identical result could also be received if we manually multiply each grade by the corresponding weight (results in column D, the formulas used in column E have been used) and add them: Example 2 – weights are not summing up to 100% or are not percentage values Obtained result (3,75) is a weighted average, so the student’s grade should be 4 (rounded to the whole number). By using the SUMPRODUCT function, we can calculate the sum of individual grades multiplied by their weights, so we get a weighted average:Įach value from column B was multiplied by the corresponding weight in column C, and then the results were added. Example 1 – weights are summing up to 100%Ī student during the semester received some grades for individual tasks, which have specific percentage weight assigned (the sum of weights is 100%):īased on this information, we want to calculate the final grade, but due to different weights for individual grades, we can’t use the arithmetic average. In the situation, that one of the used tables is bigger or smaller than the other ones, the function will return the #VALUE! error, which indicates that formula has been typed incorrectly: Examples for calculating weighted averageīelow, I will show you two practical examples, in which you can use the function SUMPRODUCT and SUM to calculate the weighted average in Excel. ![]() The SUMPRODUCT function multiplies values from B2-B7 by the values from C2-C7, and then sums up the results (B2 * C2 + B3 * C3 + B4 * C4, etc.).Īrray arguments used in the SUMPRODUCT function must have the same size. In the example above, the arguments for the function are data ranges B2 to B7 and C2 to C7. – these are arguments of the function and they define ranges of data that should be multiplied by each other and then results of multiplications are added. The SUMPRODUCT function is used to multiply values entered to data ranges and then sums them up. In such situation, we can use other available functions and one of the best for this purpose is SUMPRODUCT. In one of my the previous articles, I have presented basic methods of calculating the arithmetic average, however, Excel unfortunately does not provide any special function for calculating weighted average. From this article, you will learn how to calculate a weighted average in Excel by using the SUMPRODUCT and SUM formulas. Weighted average is a calculation, which includes various degree of importance of values.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |