Students' grade scores calculation in division base.

[LONGO]

Cand. No	Lit	Civ	His	Kisw	Bios	Math	Engl	Phy	Chem	DIV	PONTS
0001	37	45	67	89	90	23	23	34
0002	24	56	78	90	34	12	17	88	45
0003	56	78	90	78	X	33	45	55
0004	23	34	45	58	78	89	87	76
0005	X	X	X	X	X	X	X	X		ABS	--
0006	56	78	90	87	65	76	54	45	23
0007	56	78	54	45	39	00	45	56
0008	89	78	90	77	67	89	90	93	79

<tbody>
</tbody>

MARKS	GRADE	POINTS
0	F	5
30	D	4
45	C	3
65	B	2
75	A	1

<tbody>
</tbody>

POINTS	DIVISION
7-17	I
18-21	II
22-25	III
26-33	IV
34-35	0

<tbody>
</tbody>

Hello Members!
Im looking for the formula of calculating division and points for exam results for my students which will penalize the pass candidate who got grade "F" for Math and return DIV III instead of being pass in DIV I or II. Since Math is compulsory.
Thanks in Advance!



For me i did like this but i failed the condition in red:
​

removed bad image attempt

 

[AlanY]

your attached image didn't show up, try this

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J	K	L
1	Cand. No	Lit	Civ	His	Kisw	Bios	Math	Engl	Phy	Chem	DIV	PONTS
2	1	37	45	67	89	90	23	23	34		III	25
3	2	24	56	78	90	34	12	17	88	45	III	25
4	3	56	78	90	78	X	33	45	55		I	16
5	4	23	34	45	58	78	89	87	76		II	19
6	5	X	X	X	X	X	X	X	X		X	0
7	6	56	78	90	87	65	76	54	45	23	I	15
8	7	56	78	54	45	39	0	45	56		III	10
9	8	89	78	90	77	67	10	90	93	79	III	13
10	9	89	78	90	77	67	89	90	93	79	I	9
Sheet1

Cell Formulas
Range	Formula
K2	{=IFNA(LOOKUP(IF(G2<30,MAX(L2,25)-0.1,L2-0.1),{0;17;21;25;33},{"I","II","III","IV",0}),"X")}
L2	{=SUM(IFNA(LOOKUP(B2:I2,{0;30;45;65;75},{5;4;3;2;1}),0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[oldbrewer]

Cand. No	Lit	Civ	His	Kisw	Bios	Math	Engl	Phy	Chem	DIV	PONTS
1	37	45	67	89	90	23	23	34
2	24	56	78	90	34	12	17	88	45
3	56	78	90	78	X	33	45	55
4	23	34	45	58	78	89	87	76
5	X	X	X	X	X	X	X	X	X	ABS	--
6	56	78	90	87	65	76	54	45	23
7	56	78	54	45	39	0	45	56
8	89	78	90	77	67	89	90	93	79
MARKS	GRADE	POINTS
0	F	5
30	D	4
45	C	3
65	B	2
75	A	1
POINTS	DIVISION
0	I
21	II
25	III
33	IV
35	0
Im looking for formula of calculating division & points for exam results for my students which will penalize the pass candidate with "F" for Math and return DIV III instead of being pass in DIV I or II. Since Math is compulsory.
Cand. No	Lit	Civ	His	Kisw	Bios	Math	Engl	Phy	Chem
1	D	C	B	A	A	F	F	D	F
2	F	C	A	A	D	F	F	A	C
3	C	A	A	A	n/a	D	C	C	F
4	F	D	C	C	A	A	A	A	F
5	n/a	n/a	n/a	n/a	n/a	n/a	n/a	n/a	n/a
6	C	A	A	A	B	A	C	C	F
7	C	A	C	C	D	F	C	C	F
8	A	A	A	A	B	A	A	A	A
Cand. No	Lit	Civ	His	Kisw	Bios	Math	Engl	Phy	Chem
1	4	3	2	1	1	5	5	4	5		30	III	III
2	5	3	1	1	4	5	5	1	3		28	III	III
3	3	1	1	1	n/a	4	3	3	5		21	II	II
4	5	4	3	3	1	1	1	1	5		24	II	II
5	n/a	n/a	n/a	n/a	n/a	n/a	n/a	n/a	n/a		0	I	I
6	3	1	1	1	2	1	3	3	5		20	I	I
7	3	1	3	3	4	5	3	3	5		30	III	III
8	1	1	1	1	2	1	1	1	1		10	I	I
is this what you want
easy to get grade and points

<colgroup><col><col span="14"></colgroup><tbody>
</tbody>

 

[LONGO]

your attached image didn't show up, try this

Excel 2013/2016

	A	B	C	D	E	F	G	H	I	J	K	L
1	Cand. No	Lit	Civ	His	Kisw	Bios	Math	Engl	Phy	Chem	DIV	PONTS
2	1	37	45	67	89	90	23	23	34		III	25
3	2	24	56	78	90	34	12	17	88	45	III	25
4	3	56	78	90	78	X	33	45	55		I	16
5	4	23	34	45	58	78	89	87	76		II	19
6	5	X	X	X	X	X	X	X	X		X	0
7	6	56	78	90	87	65	76	54	45	23	I	15
8	7	56	78	54	45	39	0	45	56		III	10
9	8	89	78	90	77	67	10	90	93	79	III	13
10	9	89	78	90	77	67	89	90	93	79	I	9

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula K2 {=IFNA(LOOKUP(IF(G2<30,MAX(L2,25)-0.1,L2-0.1),{0;17;21;25;33},{"I","II","III","IV",0}),"X")} L2 {=SUM(IFNA(LOOKUP(B2:I2,{0;30;45;65;75},{5;4;3;2;1}),0))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Hello! Thanks for your respond it worked for me as what you did, but i have stack to combine in my formula it show too long argument in formula bar. Since i want the formula as what you did but it should select best 7 scores in raw of candidate because the first division it start with (div I point 7 for candidate who got 7 A's).

Thanks in advance!

 

[AlanY]

Hello! Thanks for your respond it worked for me as what you did, but i have stack to combine in my formula it show too long argument in formula bar. Since i want the formula as what you did but it should select best 7 scores in raw of candidate because the first division it start with (div I point 7 for candidate who got 7 A's).

Thanks in advance!

i'm in lost as what's required.
would you explain a bit more?

 

[LONGO]

Your not lost you did right but im sorry i did mistake in my first Qn.

I need the formula which it can satisfy the following condition:

Since i have two groups of students, who have to take 7 subjects or above 7 up to 10 subjects.
But calculation of division it based only on 7 subjects. So now it should look only 7 best scored subjects among all subjects in the raw
Example 67, 65,80, 90, 74, 68 89, 57, 36, 78 so here i have to select 90,89,80,78,74,68,67, including Math and if math scores F
he/she should get div III as what you did before. In addition to the 'X X X X X X X' means absent candidate he/she should
appear as ABS in column of Div with '-' in points column. Please refer to the first asked data above.

Thanks for your kindness!

 

[AlanY]

ok, a bit more complicated that I anticipated thus I use a helper table in Columns L-U that can be hidden.
since you mentioned up to 10 subjects I'd added a dummy subject (Other) to cover it.

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J	K
1	Cand. No	Lit	Civ	His	Kisw	Bios	Math	Engl	Phy	Chem	Other
2	1	37	45	67	89	90	23	23	34
3	2	24	56	78	90	34	12	17	88	45	45
4	3	56	78	90	78	X	33	45	55
5	4	23	34	45	58	78	89	87	76
6	5	X	X	X	X	X	X	X	X	X	X
7	6	56	78	90	87	65	76	54	45	23	23
8	7	56	78	54	45	39	0	45	56
9	8	89	78	90	77	67	10	90	93	79	79
10	9	89	78	90	77	67	89	90	93	79	79
Sheet2

Excel 2013/2016
	L	M	N	O	P	Q	R	S	T	U	V	W
1	Lit	Civ	His	Kisw	Bios	Math	Engl	Phy	Chem	Other	Top7	Grade
2	4	3	2	1	1	5	5	4	5	5	31	IV
3	5	3	1	1	4	5	5	1	3	3	28	IV
4	3	1	1	1	X	4	3	3	5	5	24	III
5	5	4	3	3	1	1	1	1	5	5	26	IV
6	X	X	X	X	X	X	X	X	X	X	ABS	-
7	3	1	1	1	2	1	3	3	5	5	22	III
8	3	1	3	3	4	5	3	3	5	5	28	IV
9	1	1	1	1	2	5	1	1	1	1	12	III
10	1	1	1	1	2	1	1	1	1	1	8	I
Sheet2

Cell Formulas
Range	Formula
L2	=IFNA(LOOKUP(B2,{0;30;45;65;75},{5;4;3;2;1}),"X")
V2	{=IFERROR(SUM(LARGE(L2:U2,ROW(INDIRECT("$1:$"&MIN(7,COUNT(L2:U2)))))),"ABS")}
W2	{=IFERROR(LOOKUP(IF(G2<30,MAX(V2,25)-0.1,V2-0.1),{0;17;21;25;33},{"I","II","III","IV",0}),"-")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[LONGO]

Regard the post the post above i asked how to arrange students scores in division and you response well. Thanks!
The formula it works so good. Now i need some correction on it especially
The one who get at least two S (which = 6) or one principal pass not less than E (=5) should follow in Division IV and 1 S should get DIV 0
Where A,B,C,D and E are Principal pass and S is Subsidiary.
Example: The student require to take three subjects in order to get his/her DIV.
here, A, X, X he or she should get DIV IV: S, X,X get DIV 0, E,X,X gets DIV IV , A,A,A gets DIV 1 point 7 the others point will be "-" I use the formula above but
i failed to return this.

The Guide and Points
80-100 = A
70-79= B
60-69 =C
50-59=D
40-49=E
35-39=S
0-34=F
WHERE A=1,B=2,C=3,D=4,E=5,S=6,F=7.

DIVISION:
3-9=I,
10-12=II,
13-17=III,
18-19=IV,
20-21=0

 

[LONGO]

Regard the post the post above i asked how to arrange students scores in division and you response well. Thanks!
The formula it works so good. Now i need some correction on it especially
The one who get at least two S (which = 6) or one principal pass not less than E (=5) should follow in Division IV and 1 S should get DIV 0
Where A,B,C,D and E are Principal pass and S is Subsidiary.
Example: The student require to take three subjects in order to get his/her DIV.
here, A, X, X he or she should get DIV IV: S, X,X get DIV 0, E,X,X gets DIV IV , A,A,A gets DIV 1 point 7 the others point will be "-" I use the formula above but
i failed to return this.

The Guide and Points
80-100 = A
70-79= B
60-69 =C
50-59=D
40-49=E
35-39=S
0-34=F
WHERE A=1,B=2,C=3,D=4,E=5,S=6,F=7.

DIVISION:
3-9=I,
10-12=II,
13-17=III,
18-19=IV,
20-21=0

https://www.dropbox.com/s/oep55v1dz21ni1p/PROBLEM REQUIED TO BE SOLVED.png?dl=0
[COLOR=inherit !important]


<link rel="stylesheet" type="text/css" href="moz-extension://ae186323-0ade-4617-9576-8a4b1411d75e/skin/s3gt_tooltip_mini.css"><style type="text/css" media="print">#s3gt_translate_tooltip_mini { display: none !important; }</style>[/COLOR]

 

[LONGO]

REQUIRED	SUBJECTS	WEIGHT	TO RETURN
POINT	DIV	H	K	L	POINT	DIV
3	I	90	80	90	1	1	1
13	III	17	88	45	7	1	5
12	II	45	55	67	5	4	3
5	I	87	76	77	1	2	2
-	ABS	X	X	X	X	X	X
16	III	54	45	23	4	5	7
-	IV	45	X	X	5	X	X
-	IV	30	30	X	7	7	X
-	IV	23	30	X	7	7	X

<colgroup><col style="mso-width-source:userset;mso-width-alt:1422; width:30pt" width="40" span="2"> <col style="mso-width-source:userset;mso-width-alt:1422;width:30pt" width="40"> <col style="mso-width-source:userset;mso-width-alt:1422;width:30pt" width="40"> <col style="mso-width-source:userset;mso-width-alt:1422;width:30pt" width="40"> <col style="mso-width-source:userset;mso-width-alt:1422; width:30pt" width="40" span="3"> <col style="mso-width-source:userset;mso-width-alt:1422;width:30pt" width="40"> <col style="mso-width-source:userset;mso-width-alt:1479;width:31pt" width="42"> </colgroup><tbody>
</tbody>