Excel is a perfect tool for all mathematical operations and visual presentation of data. It can be used to combine the two, as well.
In the example below, we will show how can you use Excel to calculate the average percentage of marks.
Calculate the Average Percentage of Marks
For our example, we will suppose that we had four groups of students, each group containing 15 students, and their marks from the exam, ranging from 1% to 100%:
We now need to calculate the average of our percentages for every group, and we will do so by inserting the AVERAGE formula at the bottom of our table:
Now we need to calculate the averages of our four groups. We need to consider that every group has a different number of students (for example, we have 15 students in group A, but only 11 students in group C).
To encapsulate this, we need to use the COUNT formula. We will place it beneath the averages:
To calculate the average percentage of all marks, we will use the SUMPRODUCT formula, including averages and counts of records. Our formula will be as follows:
|
1 |
=SUMPRODUCT(B17:E17,B18:E18)/SUM(B18:E18) |
This is what the formula looks like in the sheet:
And our result will be 49.40%.
To verify this result we calculate results for every group (multiplying AVERAGE and COUNTA for every group):
Then we sum all these numbers and divide them by the sum of the total students. We will get the same number:
