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

baidya91 · Jun 3, 2016

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

AliGW · Jun 4, 2016

What happens to the grand total if someone fails theory or practical in two or more of the subjects?

Presumably the summation total is an average?

AliGW · Jun 4, 2016

How about this?

Report Card

	Subject 1	Subject 2	Subject 3	Subject 4	Subject 5	Subject 6	Grand Total
	Theory	Practical	TOTAL	Theory	Practical	TOTAL	Theory	Practical	TOTAL	Theory	Practical	TOTAL	Theory	Practical	TOTAL	Theory	Practical	TOTAL	%	GRADE	%	GRADE
Student 1	25	46	71 x	45	50	95	25	3	28 x	2	12	14 x	43	42	85	43	8	51 x	90	AA	0	D
Student 2	33	48	81	29	12	41 x	22	8	30 x	35	50	85	13	27	40 x	7	12	19 x	83	A+	30	C
Student 3	33	25	58 x	27	12	39 x	16	22	38 x	18	29	47 x	0	42	42 x	35	32	67	67	A	40	B
Student 4	45	30	75	47	31	78	31	33	64	50	48	98	40	35	75	38	46	84	79	A+	50	B+
Student 5	6	10	16 x	47	32	79	34	33	67	2	28	30 x	40	4	44 x	23	45	68 x	73	A	60	A
Student 6	27	48	75 x	37	24	61 x	15	48	63 x	10	9	19 x	11	45	56 x	31	12	43 x	0	D	75	A+
Student 7	47	48	95	45	0	45 x	26	26	52 x	22	40	62 x	50	46	96	31	28	59 x	96	AA	90	AA
Student 8	25	28	53 x	10	11	21 x	45	32	77	37	12	49 x	50	1	51 x	14	20	34 x	77	A+
Student 9	7	39	46 x	44	42	86	8	4	12 x	38	8	46 x	1	24	25 x	4	8	12 x	86	A+
Student 10	1	4	5 x	48	18	66 x	26	37	63 x	19	44	63 x	47	46	93	6	40	46 x	93	AA

D5:	=IF(OR(B5<30,C5<30),SUM(B5:C5)&" x ",SUM(B5:C5))
T5:	=IFERROR(ROUND(AVERAGE(D5,G5,J5,M5,P5,S5),0),0)
U5:	=VLOOKUP(T5,$W$5:$X$11,2,1)

baidya91 · Jun 4, 2016

Thanks for replying to my query. Sir, I would like to inform you that the grand total will be the sum total of best of 5 subject totals and if anyone fails in more than one subject, he/she will be recorded to have failed. Besides, subject-wise grading has to be done in addition to overall grade. If anybody fails in any subject (theory or practical or both his/her grade in the subject will be 'D' even if the subject total is more than 30%.

baidya91 · Jun 4, 2016

FORMAT FOR EXAMPLE -

Name of the Candidate	SUBJECTS	TOTAL OF BEST OF 5 SUBJECTS	AVERAGE	OVERALL GRADE	STATUS
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+	PASSED
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+	PASSED
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+	PASSED
SUKALYAN KUNDU	62	18	80	A+	53	19	72	A	24	20	44	B	40	5	45	B	35	26	61	A	24	30	54	B+	311	62.2	A	PASSED
SABUJ MONDAL	23	20	43	D	30	5	35	D	61	30	91	AA	70	20	90	AA	60	30	90	AA	55	30	85	A+	X	X	X	FAILED

baidya91 · Jun 4, 2016

Please help me solve the problem.

AliGW · Jun 5, 2016

The problem you have presented is different to the one I solved! You have now introduced different weightings in the different subjects. It would have been better to have given all this information at the outset. I am afraid I don't have time today to do any further work on this - sorry. And, just for information, I am not a Sir!

baidya91 · Jun 5, 2016

Thanks a lot again for your response. Please extend your helping hands when you have time at your disposal. Another thing I want to add is that 'Passed' status will have to be made on the passes of at least 5 subjects and grand total has to be minimum 150 or the status will be 'Failed'. Thanks once again.

AliGW · Jun 5, 2016

OK - here you go:

Report Card

	Bengali		English		Chemistry		Maths		Physics		Biology		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	BEST 5	AVERAGE	GRADE	STATUS	%	GRADE
	80	20			80	20			70	30			80	20			70	30			70	30
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+	PASSED	0	D
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+	PASSED	30	C
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+	PASSED	40	B
SUKALYAN KUNDU	62	18	80	A+	53	19	72	A	24	20	44	B	40	5	45	D	35	26	61	A	24	30	54	B+	312	62.4	A	PASSED	50	B+
SABUJ MONDAL	23	20	43	D	30	5	35	D	61	30	91	AA	70	20	90	AA	60	30	90	AA	55	30	85	A+	X	X	X	FAILED	60	A
																													75	A+
		D6:	=B6+C6																										90	AA
		E6:	=IF(OR((B6/B$5)100<30,(C6/C$5)100<30),"D",VLOOKUP(D6,$AE$6:$AF$12,2,1))
		Z6:	=IF(OR((B7/B$5)100<30,(C7/C$5)100<30),"D",VLOOKUP(D7,$AE$6:$AF$12,2,1))
		AA6:	=IF(Z6="X","X",Z6/5)
		AB6:	=IF(Z6="X","X",VLOOKUP(AA6,$AE$6:$AF$12,2,1))
		AC6:	=IF(OR(AB6="X",AB6="D"),"FAILED",IF(Z6<150,"FAILED","PASSED"))

AliGW · Jun 5, 2016

Please note that the formula in Z6 should be:

=IF(COUNTIF(E6:Y6,"D")>1,"X",SUM(LARGE((D6,H6,L6,P6,T6,X6),{1,2,3,4,5})))

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

baidya91

Board Regular

Excel Facts

AliGW

Banned

AliGW

Banned

baidya91

Board Regular

baidya91

Board Regular

baidya91

Board Regular

AliGW

Banned

baidya91

Board Regular

AliGW

Banned

AliGW

Banned

Similar threads

Forum statistics

Share this page

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

Board Regular

Excel Facts

Banned

Banned

Board Regular

Board Regular

Board Regular

Banned

Board Regular

Banned

Banned

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock