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. Hope you will me solve the problem.

Thanks in advance

 

[baidya91]

Sorry to disturb you again. The formulas given to me are working perfectly where the number of total subjects is 6 but the when the number of subjects exceeds 6 it is not working. In fact, in the table, there are more than 6 subjects. Sometimes it is 10. But the students have sat for only either 6 or 5 subjects. In the table the subjects they have not appeared in are left blank. Besides, "D" Graded subjects are being taken into accounts in the best of five total if they are greater than other subjects not graded "D". Please tell me how to solve the problem. For Example, in the following case the actual best of five total will be 239 but it is now 246 according to the formulas given from your end.

Name of the Candidate

SUBJECTS

TOTAL OF BEST OF 5 SUBJECTS

BENGA

TOTAL

GRADE

ENGB

TOTAL

GRADE

COMA

TOTAL

GRADE

EDCN

TOTAL

GRADE

GEGR

TOTAL

GRADE

SANS

TOTAL

GRADE

HIST

TOTAL

GRADE

PHIL

TOTAL

GRADE

POLS

TOTAL

GRADE

PHED

TOTAL

GRADE

ACTUAL TOTAL OF BEST OF 5 SUBJECTS

<tbody>
</tbody>

80	20	80	20	70	30	80	20	70	30	80	20	80	20	80	20	80	20	40	60
TH	PR	TH	PR	TH	PR	TH	PR	TH	PR	TH	PR	TH	PR	TH	PR	TH	PR	TH	PR
SOUVIK DAS	38	16	54	B+	35	17	52	B+	21	15	36	C	24	17	41	B			0	D			0	D	40	16	56	B+			0	D	23	20	43	D			0	D	246	239

<tbody>
</tbody>

 

[AliGW]

You've shifted the goalposts AGAIN! Please, it is taking me a considerable amount of my own time, voluntarily, to help you, so you need to explain what you are trying to do in its entirety before I do anything further. Having said this, I think you have been given enough to be able to adapt the formulae YOURSELF to make them work for the new scenario. This is not a commercial services forum! When you have explained your requirements fully, I may have one more go at it if I can find the time, but it will be the last time, so make sure you include EVERYTHING.

 

[baidya91]

First, I apologize for the disturbances caused to you by me. I am really sorry to have wasted much of your valuable time and energy. In fact, I am entirely new to such a forum. Besides, I thought the formulas would work in any situation be it six or more subjects. I have tried to slightly change the formulas to suit my purpose when number of subjects exceeds 6 but that is not working (For example. =IF(COUNTIF(E6:Y6,"D")>1,"X",SUM(LARGE((D6,H6,L6,P6,T6,X6),{1,2,3,4,5}))) <Change>=IF(COUNTIF(E6:Y6,"D")>2 or 3,"X",SUM(LARGE((D6,H6,L6,P6,T6,X6),{1,2,3,4,5}))). That works for 7 subjects but not for more than 7.) In fact, there are as many as 10 subjects and the students can opt for minimum 5 and maximum 6 subjects from the table of 10 subjects but the grand total will be on the total of best of 5 subjects and the "D" graded subject(s) will always be excluded from the grand total even if they are greater than non-D graded other subjects as I mentioned from the very start. And the columns of the subjects which the students have not opted for are being left blank during tabulation. Other criteria will remain unchanged. Once again, I regret for the wastage of your valuable time and energy. Hope you'll forgive me and help me work out the problem.

 

[AliGW]

Last attempt, which should meet all of your criteria. Remember that the D6 and E6 formulae need to be copied down and then over to the other subjects' total and grade columns for this to work.

Report Card
	Bengali		English		Chemistry		Maths		Physics		Biology		Subject 7		Subject 8		Subject 9		Subject 10		Grand Total
	Theory	Practical	TOTAL	GRADE	Theory	Practical	TOTAL	GRADE	Theory	Practical	TOTAL	GRADE	Theory	Practical	TOTAL	GRADE	Theory	Practical	TOTAL	GRADE	Theory	Practical	TOTAL	GRADE	Theory	Practical	TOTAL	GRADE	Theory	Practical	TOTAL	GRADE	Theory	Practical	TOTAL	GRADE	Theory	Practical	TOTAL	GRADE	BEST 5	AVERAGE	GRADE	STATUS
	80	20			80	20			70	30			80	20			70	30			70	30			70	30			70	30			70	30			70	30
Student 1	67	19	86	A+	64	20	84	A+	56	29	85	A+	61	19	80	A+	55	29	84	A+	48	30	78	A+	48	30	78	A+	48	30	78	A+	48	30	78	A+	48	30	78	A+	419	83.8	A+	PASSED
Student 2	55	19	74	A	63	20	83	A+	54	29	83	A+	43	19	62	A	54	27	81	A+	42	30	72	A	42	30	72	A	42	30	72	A	42	30	72	A	42	30	72	A	393	78.6	A+	PASSED
Student 3	61	19	80	A+	60	20	80	A+	57	28	85	A+	52	18	70	A	53	25	78	A+	45	30	75	A+	45	30	75	A+	45	30	75	A+	45	30	75	A+	45	30	75	A+	398	79.6	A+	PASSED
Student 4	62	18	80	A+	53	19	72	A	24	20	44	B	40	5	45 x	D	35	26	61	A	24	30	54	B+	24	30	54	B+	24	30	54	B+	24	30	54	B+	24	30	54	B+	311	62.2	A	PASSED
Student 5	23	20	43 x	D	30	5	35 x	D	61	30	91	AA	70	20	90	AA	60	30	90	AA	55	30	85	A+	55	30	85	A+	55	30	85	A+	55	30	85	A+	55	30	85	A+	X	X	X	FAILED
		D6:	=IF(AND(ISBLANK(B6),ISBLANK(C6)),"",IF(OR((B6/B$5)*100<30,(C6/C$5)*100<30),B6+C6&" x",B6+C6))	%	GRADE
		E6:	=IF(D6="","",IF(OR((B6/B$5)*100<30,(C6/C$5)*100<30),"D",VLOOKUP(D6,$N$14:$O$20,2,1)))
		AP6:	=IF(COUNTIF(E6:AO6,"D")>1,"X",SUM(LARGE((D6,H6,L6,P6,T6,X6,AB6,AF6,AJ6,AN6),{1,2,3,4,5})))		0	D
		AQ6:	=IF(AP6="X","X",AP6/5)								30	C
		AR6:	=IF(AP6="X","X",VLOOKUP(AQ6,$N$14:$O$20,2,1))						40	B
		AS6:	=IF(OR(AR6="X",AR6="D"),"FAILED",IF(AP6<150,"FAILED","PASSED"))				50	B+
													60	A

<tbody>
</tbody>

 

[baidya91]

Thank you a lot though I have not yet been able to test your formulas.

 

[AliGW]

They work for what you have said they want. You certainly have enough information now to be able to adapt them slightly if you need to.

 

[baidya91]

As I am out of station now, I cannot tell you what the actual situation is. However, I shall inform you later.

 

[baidya91]

Thanks a lot for your painstaking labour in helping me out to solve the problem.

 

[AliGW]

Glad we got there in the end and that my efforts were of use to you. Thanks for letting me know.