Prepare the Mark Sheet of a School Student using Ms-Excel. (Full Detailed).

Consider the following Mark Sheet of a School and write the formula for the asked questions.

• Insert the Ten students' names with their marks obtained in the exam.
• Calculate Total, Percentage.
• Find out the Result as follows.
• If the student has equal to or more than 35 marks in all subjects, students have passed otherwise fail.
• If the student has passed, then displays division as follows.
• If percentage>80, distinction, if per>60 and <80, first division, if per<60 and >45, Second division, if per >45 and <32, third division otherwise display "***".
• Find out the Rank of all Students.

Solution:

This question most important which is asked frequently in competitive exams, entrance exams, bank exams, governmental exams, interview exams, public service commission exam or any other exam. 

I think that writing an ms-excel formula is easy for you when you compare the question condition with your natural language. Write a question in your Language and convert it into an algorithm like code for it to solve. 

For e.g.

if you come after 10:00 a.m., you will be charged as 5% of your Salary. 

Algorithm code as

Let's you come after  10:00 a.m., then

If(Come Time>10, Salary-Salary*5%)

Now write the actual formula for it in your excel cell.

If you use this for all the conditions, you sure write the right formula for this question as well as for other questions also. Practice questions by writing the first algorithm then convert it into the right formula. If you did this, you sure complete this question in your job exam.

Below is the detailed solution to the above question, how to solve step by step.

Write the formula for the

Total: =SUM(D4:H4)
In I4 cell, use the sum formula and gives the range in the parameter of the formula.
Range means cells range which you want to calculate; From D4 cell to H4 cell.
Also, you can sum cells one by one as below.
=(D4+E4+F4+G4+H4)
Now for other cells, copy the formula by Dragging down. By Dragging down cells value, you need not write manually formula to other cells. 
Now type "=sum(d4:h4)" to the cell I4 cell.
Percentage: =I4/5
To calculate percentage;
Use the formula: Total Marks/Number of subjects
Percentage: I4/5
Now type "I4/5" in the cell J4 Cell.
Result: =IF(AND(D4>=32,E4>=32,F4>=32,G4>=32,H4>=32,H4>=32),"Pass","Fail")
To write the formula for the Result Field, You think a little bit on, "If the student has equal to or more than 35 marks in all subjects, students have passed otherwise fail." 
In condition, In all subjects means you have to check all values of the subject, which AND Condition does. So you have use AND condition with IF Condition.
Lets writing algorithm code for this, 
if all subjects>=32, pass otherwise fail,
Now writing the actual formula for the above algorithm as follows.
Type the formulla in Cell K4 Cell, 
=IF(AND(D4>=32,E4>=32,F4>=32,G4>=32,H4>=32,H4>=32),"Pass","Fail")
After this drag cell to other cells.
Division: =IF(AND(K4="Pass",J4>=80),"Distinction",IF(AND(K4="Pass",J4>=60,J4<80),"First Division",IF(AND(K4="Pass",J4>=45,J4<60),"Second Division",IF(AND(K4="Pass",J4>=32,J4<45),"Third Division","*****"))))
For calculating Division, Use the Two parameter one Pass parameter and other is Percentage. The student must pass, if student pass than use the logical conditions to calculate Division.
Type "=IF(AND(K4="Pass",J4>=80),"Distinction",IF(AND(K4="Pass",J4>=60,J4<80),"First Division",IF(AND(K4="Pass",J4>=45,J4<60),"Second Division",IF(AND(K4="Pass",J4>=32,J4<45),"Third Division","*****"))))" formula in the L4 cell. Copy the cell to other cells by dragging down the value to the other cells.

Rank: =RANK(J4,$J$4:$J$13,0)
Now before calculating this field, we must know about the Absolute Reference. See the definition of Absolute Reference
Absolute Reference: We want to compare the number to the list of numbers in cells $B$2:$B$11. 
Use an absolute reference ($B$2:$B11), instead of a relative reference (B2:B11) so the referenced range will stay the same when you copy the formula down to the cells below. 
Let me explain about why we here use Absolute Reference why not Just the Range of Cells
for e.g. =RANK(J4, (J4,$J$4:$J$13,0)
The RANK formula ranks the value in Ascending or Descending order comparing to the first parameter value according to the last parameter. 
If the last parameter value assigns to 0 then rank displays in ascending order, 1 for descending order. 
The value of the first parameter comparing to value one by from Cell J4 to Cell J13, then displays it's rank in that associated cell ascending or descending order. If you use the Relative Reference, it gets not formula error but gets the wrong results. If you want to know why it gets an error, lately I'll discuss it in the post.

Rank: =RANK(J4,$J$4:$J$13,0)
Now Type the "=RANK(J4,$J$4:$J$13,0)" formula to the M4 cell as you see above figure.

If you have any queries, related to this question, ask me in the comment section without any hesitation. I would like to say thank you if this post is helpful for you or if I solve your problem through this question