Calculate the Average Percentage of Marks in Excel

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%:

Table

Description automatically generated

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:

Table

Description automatically generated

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:

Table

Description automatically generated

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:

This is what the formula looks like in the sheet:

Graphical user interface, table, Excel

Description automatically generated

And our result will be 49.40%.

To verify this result we calculate results for every group (multiplying AVERAGE and COUNTA for every group):

Chart, table

Description automatically generated

Then we sum all these numbers and divide them by the sum of the total students. We will get the same number:

Chart, table

Description automatically generated

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.