Marksheet in Excel

Let us understand how to create a mark sheet in Excel. Suppose we have the following data for marks in various subjects by 120 students.

We want to find the total marks scored, an average of marks (this will also help us to give students grades), and a result on whether the student passed or failed.

To find out the total, we will use the SUM function.

The syntax for the SUM in excel is as follows:

This function takes 255 numbers in this way to add. But we can also give the range for more than 255 numbers as an argument for the function, to sum up.

There are various methods to specify numbers as follows:

#1 - Comma Method

The total will be –

In this method, we use commas for specifying and separating the arguments. We have fixed or selected various cells with commas.

#2 - Colon Method (Shift Method)

In this method, we have used the “Shift” key after selecting the first cell (E3) and then used the right arrow key to choose cells until I3. We can select continuous cells or specify the range with the colon manually.

The total will be:

After entering the formula for the first student, we can copy the formula using “Ctrl+D” as a shortcut key after selecting the range with the first cell at the top to copy this formula down.

Apply the above formula to all the remaining cells. We get the following result.

For calculating average marks, we will use the AVERAGE function. The syntax for the AVERAGE function is the same as the SUM function.

This function returns the average of its arguments.

We can pass arguments to this function the same way we pass arguments to the SUM function.

For evaluating the average in the excel mark sheet, we will use the AVERAGE function in the following way. First, we will select marks scored by a student in all five subjects.

The average will be –

We will use Ctrl+D to copy down the function.

Apply the above formula to all the remaining cells. We get the following result.

We can see that we have got values in decimal for average marks, which doesn’t look good. So, we will use the ROUND function to round the values to the nearest integer.

This function is used to round the values to the specified number of digits.

The syntax for the ROUND function in excel is as follows:

Arguments Explanation

• Number: We need to provide the number we want to round for this argument. We can reference the cell containing a number or specify the number itself.
• Num_digits: In this argument, we specify the number of digits we want after the point in the number. If we want a pure integer, then we define 0.

Let us use this function in the Excel mark sheet. First, we will wrap up the AVERAGE function with the ROUND function to round the number, which the AVERAGE function will return.

We have used the AVERAGE function for the number argument and 0 for num_digits.

After pressing the “Enter” key, we will get the desired result, i.e., a number with no decimal digit.

The average will be:

Apply the above formula to all the remaining cells. We get the following result.

Now to find out the grade, we have the following criteria.

• If the student has scored average marks greater than or equal to 90, then the student will get a grade of S
• If the student has scored average marks greater than or equal to 80, then the student will get a grade of A+
• If the student has scored average marks greater than or equal to 70, then the student will get a grade A
• If the student has scored average marks greater than or equal to 60, the student will get a grade of B+.
• If the student has scored average marks greater than or equal to 35, then the student will get a grade of B
• If the student has scored average marks less than 35, the student will get a grade of F.

We will use the IF function in excel multiple times to apply these criteria. It is called NESTED IF in excel, also as we will use the IF function to give an argument to the IF function itself.

We have used the following formula to evaluate the Excel mark sheet grades.

Let us understand the logic applied in the formula.

As we can see for “logical_test,” which is the criterion, we have given a reference to the K3 cell containing AVERAGE of marks and have used logical operators, “Greater Than” and “Equal To,“ then compared the value with 90.

If the average mark scored by the student is greater than or equal to 90, then write the value which we will specify in the “value_if_true” argument. On the other hand, if this criterion is not satisfied by the average marks, what should be written in the cell as “Grade,” that we will specify for the “value_if_false” argument.

For the “value_if_true” argument, we will specify text (Grade) within double quotes, “S.”

For the “value_if_false” argument, we will start writing the IF function again as we have many more criteria and the corresponding grade to assign if this criterion is not satisfied.

We have started writing the IF function again for the “value_if_false” argument and specified the criteria to compare average marks with 80 this time.

The result will be:

If average marks are greater than or equal to 70 but less than 80 (first IF function criteria), the student will get an “A” grade.

We will apply the IF function in the same formula five times, as we have 6 criteria.

Make sure we have opened brackets for the IF function five times. After that, we need to close all brackets.

To find out whether a student is “PASSED” or “FAILED,” we have to apply the following criteria:

• If the student has scored greater than 200 as total marks and scored greater than 33 in all subjects, then the student is “PASSED.”
• If a student has scored less than 33 in 1 or 2 subjects and total marks are greater than 200, the student has got “ER” (Essential Repeat).
• If the student has scored less than 33 in more than 2 subjects or less than or equal to 200 as total marks, then the student is “FAILED.”

We need to evaluate several subjects where a student has scored less than 33. Then, we need to use the COUNTIF function to count numbers based on the specified criterion.

The syntax for the COUNTIF function is as follows:

Arguments

• Range: We need to reference the cells containing a number to compare the criterion with.
• Criteria: To specify the criterion, we can use logical operators to count only those numbers to satisfy the criterion.

AND Function

The syntax for AND function excel is as follows:

In the AND function, we must specify the criteria. If all the criteria are satisfied, then only TRUE comes. We can set up 255 criteria.

The formula which we have applied is as follows:

As this can be seen, we have used the AND function inside the IF function to give multiple criteria and the COUNTIF function inside the AND function to count the number of subjects in which a student has scored greater than or equal to 33.

The result will be:

Apply the above formula to all the remaining cells. We get the following result.

• We must ensure to close the brackets for the IF function.
• While specifying any text in the function, we must use double quotes (” “) as we have used while writing “Passed,” “Failed,” “ER,” etc.