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

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

<tbody>
</tbody>