As in the calculation formula Average said, the role of each observation and value in calculating the average is equal and the same. If it is necessary that some observations have more or less importance in calculating the mean, weight (importance level) can be considered for them. In this article from Arman Computer Magazine, we will examine the weighted mean and its applications. We will also review how to calculate it in Excel.
Weighted average and its applications
Simple average or arithmetic mean is used when the observations (statistical data) have the same importance, but if these data have different importance, it is necessary Give importance or weight to the observations. As you know, the average plays the role of the center of gravity. If the mass of the object is distributed unevenly so that some points have more mass and some have less mass, the weighted mean is used to obtain the center of gravity.
What is weighted Mean?
As an example, we will introduce the grade point average of university courses. When we intend to obtain the grade point average of a semester, considering that the grades of different courses do not have the same value and the importance of each course is determined by the number of course units, we should use the weighted average or the weighted grade point average instead of the average. Suppose, for example, the statistics course is 4 units, English language is 2 units, etc. If we just add the grades together and divide them by their number or the number of semester units, we have obtained an arithmetic mean that the importance of each course in the semester grade point average is the same. In order to show the importance of each course and determine their role in the semester average, it is necessary to first multiply each observation by its weight and then add up the results and divide by the total weights. The calculation formula for the weighted average is as follows.
$$ \bar{X}_w = \sum_{i=1}^n w_i x_i , \;\; 0 \leq w_i \leq 1, \;\; \sum w_i = 1 $$
Note: It is clear that the weights ($w_i$) must be non-negative and less than or equal to 1.
An example for calculating the weighted average
The following table shows the grades and the number of units of a student’s courses in the second semester of the university.
| Lesson name | course grade (from 20) | unit number | Weight of each lesson |
| Mathematics | 19 | 2 | 2/18 = 0.11 |
| Basics of Sociology | 17 | 2 | 2/18 = 0.11 |
| Psychology | 16 | 2 | 2/18 = 0.11 |
| Getting to know autism disease | 18 | 2 | 2/18 = 0.11 |
| Workshop | 20 | 1 | 1/18 = 0.06 |
| Health and first aid | 19 | 2 | 2/18 = 0.11 |
| Social Work | 20 | 2 | 2/18 = 0.11 |
| English | 17/75 | 3 | 3/18 = 0.17 |
| Science of family and population | 11/5 | 2 | 2/18 = 0.11 |
| Total | 158/25 | 18 | 1 |
Since the number of units determines the importance of the grade of each subject in the GPA, we make them as weights. For this purpose, it is sufficient to divide each unit into the sum of units to obtain weights with positive values and less than one.
Note: Note that the sum of the weights must always equal 1.
The average of this semester of the student is calculated as follows.
19 x 0.11 + 17 x 0.11 + 16 x 0.11 + 18 x 0.11 + 20 x 0.06 + 19 x 0.11 + 20 x 0.11 + 17.75 x 0.17 + 11.5 x 0.11 = 17.46
While if we were to use the arithmetic mean (without considering the weight) to calculate the grade point average, we would reach the value of 17.58.
Calculation of weighted mean in Excel
The SUMPRODUCT function is used to calculate the weighted average in Excel. Of course, remember that in this function, the weights must be specified as positive values and smaller than 1.
The image below specifies the parameters of this function. You can consider the first parameter (Array1) as the weight and the second parameter (Array2) as the index values.
Note: Next parameters (third and later) are not applicable for calculating the weighted mean.
