Seeking Help in the Preparation of Report Card or Mark Sheet of an Exam by using Excel Formula

[baidya91]

I want to prepare a mark-sheet of an examination (in excel format by putting formulas) where the students have to sit for six papers (100 marks each), each divided into two parts – theory and practical – pass-mark – 30% of full marks in theory and 30% of full mark in practical. But failure in either theory or practical means failure in the subject concerned. The grand total of six subjects will be made on the summation of best of five subjects where the marks of the subject will be excluded even if it is higher than other subject totals if the students fail either in theory or practical of the subject. Subject-wise grading and overall grading will have to be marked by putting AA for marks more than 89, A+ for than 74, A for more than 59, B+ for more than 49, B for more than 39, C for more than or equal to 30 and the rest D. Please help me prepare the mark-sheet such as follows: -

[TABLE="width: 963, align: center"]
<tbody>[TR]
[TD]Name of the Candidate
[/TD]
[TD="colspan: 8"]COMPULSORY SUBJECTS
[/TD]
[TD="colspan: 11"]ELECTIVE OPTIONAL
[/TD]
[TD="colspan: 6"]
[/TD]
[TD]TOTAL OF BEST OF 5 SUBJECTS
[/TD]
[TD]AVERAGE
[/TD]
[TD]OVERALL GRADE
[/TD]
[/TR]
[TR]
[TD="colspan: 2"]BENGA
[/TD]
[TD]TOTAL
[/TD]
[TD]GRADE
[/TD]
[TD="colspan: 2"]ENGB
[/TD]
[TD]TOTAL
[/TD]
[TD]GRADE
[/TD]
[TD="colspan: 2"]CHEM
[/TD]
[TD]TOTAL
[/TD]
[TD]GRADE
[/TD]
[TD="colspan: 2"]MATH
[/TD]
[TD]TOTAL
[/TD]
[TD]GRADE
[/TD]
[TD="colspan: 2"]PHYS
[/TD]
[TD="colspan: 2"]TOTAL
[/TD]
[TD]GRADE
[/TD]
[TD="colspan: 2"]BIOS
[/TD]
[TD]TOTAL
[/TD]
[TD]GRADE
[/TD]
[/TR]
[TR]
[TD]80
[/TD]
[TD]20
[/TD]
[TD]80
[/TD]
[TD]20
[/TD]
[TD]70
[/TD]
[TD]30
[/TD]
[TD]80
[/TD]
[TD]20
[/TD]
[TD]70
[/TD]
[TD]30
[/TD]
[TD]70
[/TD]
[TD]30
[/TD]
[/TR]
[TR]
[TD]TH
[/TD]
[TD]PR
[/TD]
[TD]TH
[/TD]
[TD]PR
[/TD]
[TD]TH
[/TD]
[TD]PR
[/TD]
[TD]TH
[/TD]
[TD]PR
[/TD]
[TD]TH
[/TD]
[TD]PR
[/TD]
[TD]TH
[/TD]
[TD]PR
[/TD]
[/TR]
[TR]
[TD]BIKRAM KAPAT
[/TD]
[TD]67
[/TD]
[TD]19
[/TD]
[TD]86
[/TD]
[TD]A+
[/TD]
[TD]64
[/TD]
[TD]20
[/TD]
[TD]84
[/TD]
[TD]A+
[/TD]
[TD]56
[/TD]
[TD]29
[/TD]
[TD]85
[/TD]
[TD]A+
[/TD]
[TD]61
[/TD]
[TD]19
[/TD]
[TD]80
[/TD]
[TD]A+
[/TD]
[TD]55
[/TD]
[TD]29
[/TD]
[TD="colspan: 2"]84
[/TD]
[TD]A+
[/TD]
[TD]48
[/TD]
[TD]30
[/TD]
[TD]78
[/TD]
[TD]A+
[/TD]
[TD]419
[/TD]
[TD]83.8
[/TD]
[TD]A+
[/TD]
[/TR]
[TR]
[TD]PRITAM GHOSH
[/TD]
[TD]55
[/TD]
[TD]19
[/TD]
[TD]74
[/TD]
[TD]A
[/TD]
[TD]63
[/TD]
[TD]20
[/TD]
[TD]83
[/TD]
[TD]A+
[/TD]
[TD]54
[/TD]
[TD]29
[/TD]
[TD]83
[/TD]
[TD]A+
[/TD]
[TD]43
[/TD]
[TD]19
[/TD]
[TD]62
[/TD]
[TD]A
[/TD]
[TD]54
[/TD]
[TD]27
[/TD]
[TD="colspan: 2"]81
[/TD]
[TD]A+
[/TD]
[TD]42
[/TD]
[TD]30
[/TD]
[TD]72
[/TD]
[TD]A
[/TD]
[TD]393
[/TD]
[TD]78.6
[/TD]
[TD]A+
[/TD]
[/TR]
[TR]
[TD]ASIS BISWAS
[/TD]
[TD]61
[/TD]
[TD]19
[/TD]
[TD]80
[/TD]
[TD]A+
[/TD]
[TD]60
[/TD]
[TD]20
[/TD]
[TD]80
[/TD]
[TD]A+
[/TD]
[TD]57
[/TD]
[TD]28
[/TD]
[TD]85
[/TD]
[TD]A+
[/TD]
[TD]52
[/TD]
[TD]18
[/TD]
[TD]70
[/TD]
[TD]A
[/TD]
[TD]53
[/TD]
[TD]25
[/TD]
[TD="colspan: 2"]78
[/TD]
[TD]A+
[/TD]
[TD]45
[/TD]
[TD]30
[/TD]
[TD]75
[/TD]
[TD]A+
[/TD]
[TD]398
[/TD]
[TD]79.6
[/TD]
[TD]A+
[/TD]
[/TR]
[TR]
[TD]SUKALYAN KUNDU
[/TD]
[TD]62
[/TD]
[TD]18
[/TD]
[TD]80
[/TD]
[TD]A+
[/TD]
[TD]53
[/TD]
[TD]19
[/TD]
[TD]72
[/TD]
[TD]A
[/TD]
[TD]39
[/TD]
[TD]27
[/TD]
[TD]66
[/TD]
[TD]A
[/TD]
[TD]11
[/TD]
[TD]18
[/TD]
[TD]29
[/TD]
[TD]D
[/TD]
[TD]35
[/TD]
[TD]26
[/TD]
[TD="colspan: 2"]61
[/TD]
[TD]A
[/TD]
[TD]24
[/TD]
[TD]30
[/TD]
[TD]54
[/TD]
[TD]B+
[/TD]
[TD]333
[/TD]
[TD]66.6
[/TD]
[TD]A
[/TD]
[/TR]
</tbody>[/TABLE]