Formula help (lowest figures with mandatory inclusions)

[ExcellingAtLife]

Hi guys,

One of our admin is currently calculating exam marks manually, and with over 200 to calculate I've offered to help. However, I've hit a blank...

We host an exam series with two types of exam and the student final scores are their highest three scores BUT one score must come from exam type A and one score from exam type B (I didn't realise this and just started using the LARGE function to SUM the three highest scores across all 6 exams which was incorrect).

I'm stumped, I started trying using LARGE and just selecting the cells with exam scores for type A to find the highest score for type A, then repeating for B, but then I can't get my head around the next step because technically all three highest scores could some from one exam type, and plus some students have only attended exam type A, missing type B (or vice versa), so shouldn't have an overall score as they've failed by default... Help!

Below is an idea of what the spreadsheet looks like (obvs anonymised) to give you an idea of what it looks like

 

[Asbestos_Jen]

My solution uses helper columns, but they could be combined into long formulas instead.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1		Exam A1	Exam A2	Exam B1	Exam A3	Exam B2	Exam B3	Total		AutoFail	1st A	2nd A	3rd A	1st B	2nd B	3rd B		sum 1st A, 1st B and highest of 2nd&3rd A and 2nd&3rd B
2	Adam	48	44	40	45	35	39				48	45	44	40	40	39		133
3	Keith	49	45	37	48	29	33				49	48	45	37	37	33		134
4	David	41	43	37	39	36	40				43	41	39	40	39	37		124
5	James	27	25		11					Fail	27	25	11	#NUM!	#NUM!	#NUM!		Fail
Sheet1

Cell Formulas
Range	Formula
J2:J5	J2	=IF(OR(ISERROR(K2),ISERROR(N2)),"Fail","")
K2:K5	K2	=LARGE((B2:C2,E2),1)
L2:L5	L2	=LARGE($B2:$E2,2)
M2:M5	M2	=LARGE($B2:$E2,3)
N2:N5	N2	=LARGE((F2:G2,D2),1)
O2:O5	O2	=LARGE($D2:$G2,2)
P2:P5	P2	=LARGE($D2:$G2,3)
R2:R5	R2	=IF(J2="Fail","Fail",SUM(K2,N2,MAX(L2:M2,O2:P2)))

 

[felixstraube]

Can you tell us what version of excel you are using?

 

[Asbestos_Jen]

Edited: I forgot to stagger the $ for some cells.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1		Exam A1	Exam A2	Exam B1	Exam A3	Exam B2	Exam B3	Total		AutoFail	1st A	2nd A	3rd A	1st B	2nd B	3rd B		sum 1st A, 1st B and highest of 2nd&3rd A and 2nd&3rd B
2	Adam	48	44	40	45	35	39				45	48	44	40	39	35		133
3	Keith	49	45	37	48	29	33				48	49	45	37	33	29		134
4	David	41	43	37	39	36	40				41	43	39	40	37	36		124
5	James	27	25		11					Fail	25	27	11	#NUM!	#NUM!	#NUM!		Fail
Sheet1

Cell Formulas
Range	Formula
J2:J5	J2	=IF(OR(ISERROR(K2),ISERROR(N2)),"Fail","")
K2:K5	K2	=LARGE(($B2:$C2,$E2),2)
L2:L5	L2	=LARGE(($B2:$C2,$E2),1)
M2:M5	M2	=LARGE(($B2:$C2,$E2),3)
N2:N5	N2	=LARGE(($F2:$G2,$D2),1)
O2:O5	O2	=LARGE(($F2:$G2,$D2),2)
P2:P5	P2	=LARGE(($F2:$G2,$D2),3)
R2:R5	R2	=IF(J2="Fail","Fail",SUM(K2,N2,MAX(L2:M2,O2:P2)))

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[felixstraube]

Here another solution:

ExamResult.xlsx
	A	B	C	D	E	F	G	H	I	J
1		Exam A1	Exam A2	Exam B1	Exam A3	Exam B2	Exam B3	Formula		Expected result
2	Adam	48	44	40	45	35	39	133		133
3	Keith	49	45	37	48	29	33	134		134
4	David	41	43	37	39	36	40	124		124
5	James	27	25		11			Fail
Hoja1

Cell Formulas
Range	Formula
H2:H5	H2	=IFERROR(LARGE((B2:C2,E2),1)+LARGE((D2,F2:G2),1)+MAX(IFERROR(LARGE((B2:C2,E2),2),0),IFERROR(LARGE((D2,F2:G2),2),0)),"Fail")