Data Validation - HELP Needed Please

[bjackson3]

Trying to create a spreadsheet that helps me track my students high school coursework. Have been using Data Validation with drop down lists because it seems to keep everything neat and simple. Have run into a problem, and do not know what to do. Will be posting HTML below, but in a nutshell, here is issue:

Grading scale is different this year versus previous years. This year an A=90-100; B=89-80; C=79-70 and D=69-60 Previous years grading scale was A=93-100; B=85-92; C=84-77 and D=76-70. This was all for a a 1 credit course.

An "A" is "worth" 4 points, B 3 points, C 2 points and D 1point. Of course, if course completed was a 1/2 credit then all of above point worths change by half.

Some courses may be HONORS and some may be AP level. Point values change for these courses as well. Any HONORS course will have an additional .5/.25 points added to worth and and AP course will have an additional 1/.5 points.

I want to be able to simply click and choose COURSE, CREDIT, GRADE, Pre 16-17, HONORS/AP and have the QPts show up depending on what was selected.

Am I trying to do too much?

Here is the MrExcel HTML code:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1
2
3
4	COURSE				CREDIT		GRADE		HON/AP		PRE 16-17?		Q. PTS
5	English 1				1		82		NO		YES		2
6	English 2CP				1		82		NO		NO		3
7	English 3H				1		82		HONORS		NO		3.5
8
9
10					For 1 Credit Course						For .5 Credit Course
11	Pre 16-17 Grade Scale				Q PTS		If Hon		If AP		Q PTS		If Hon		If AP
12	A = 100-93				4		4.5		5		2		2.25		2.5
13	B = 92-85				3		3.5		4		1.5		1.75		2
14	C = 84-77				2		2.5		3		1		1.25		1.5
15	D = 76-70				1		1.5		2		0.5		0.75		1
16
17
18					For 1 Credit Course						For .5 Credit Course
19	16-17 & Beyond Scale				Q PTS		If Hon		If AP		Q PTS		If Hon		If AP
20	A = 100-90				4		4.5		5		2		2.25		2.5
21	B = 89-80				3		3.5		4		1.5		1.75		2
22	C = 79-70				2		2.5		3		1		1.25		1.5
23	D = 69-60				1		1.5		2		0.5		0.75		1
24
25
26	Formula in Sentence form: If credit equal 1 and Pre 16-17 equals yes and grade between 93 and 100 and
27	honors/ap equals no then QPts equal 4 Else If credit equals .5 and Pre 16-17 equals yes and grade between
28	93 and 100 and honors/ap equals no then QPts equal 2 Else if credit equals 1 and Pre 16-17 equals yes and grade
29	between 93 and 100 and honors/ap equals HONORS then QPts equals 4.5 else if credit equals .5 and Pre 16-17
30	equals yes and grade between 93 and 100 and honors/ap equals HONORS then QPts equals 2.25
31
32	Would have to repeat all of above REPLACING "honors" with "AP" and changing QPts accordingly
33
34	THEN
35
36	Repeat for QPts if Pre 16-17 equals NO and change QPts
37
38	THEN
39
40	Repeat all for other grade levels
41
42	There has to be a simpler way.
43
Sheet1

Cell Formulas
Range	Formula
M12	=4.5/2
M13	=3.5/2
M14	=2.5/2
M20	=4.5/2
M21	=3.5/2
M22	=2.5/2

 

[DRSteele]

I assembled this example algorithm that might help you track your students' (with the apostrophe) high school coursework regarding the English language.

I assume you will have Data Validation for each section of the record, as I do for Pre1617, Credit and Honours/AP.

	A	B	C	D	E	F	G	H	I
1	Data Validation:	CREDIT		HON/AP		PRE1617
2		Full		AP		NO
3		Half		HONOURS		YES
4				NO
5
6				Full-Credit Course			Half-Credit Course
7	Grade	Pre16173	Post1617	Q PTS	If Hon	If AP	Q PTS	If Hon	If AP
8	A	100	100	4	4.5	5	2	2.25	2.5
9	B	92	89	3	3.5	4	1.5	1.75	2
10	C	84	79	2	2.5	3	1	1.25	1.5
11	D	76	69	1	1.5	2	0.5	0.75	1
12	F	70	60	0	0	0	0	0	0
13
14	RECORDS
15	COURSE	CREDIT	GRADE	HON/AP	PRE1617	Q. PTS
16	English 1	Full	82	NO	YES	2
17	English 2CP	Full	82	NO	NO	3
18	English 3H	Full	82	HONOURS	NO	3.5
19	English Possessives	Half	75	AP	YES	1
20	English Pronouns	Half	5	NO	NO	0
21	English SpellIng	Half	17	HONOURS	YES	0

<tbody>
</tbody>

Sheet27​

Worksheet Formulas Cell Formula F16 =INDEX($D$8:$I$12,MATCH(C16,IF(E16="yes",$B$8:$B$12,$C$8:$C$12),-1),LOOKUP(B16,$B$2:$B$3,{1;2})*LOOKUP(D16,$D$2:$D$4,{3;2;1})) <tbody> </tbody>

<tbody>
</tbody>

 

[bjackson3]

Thank
You! I will check it out ASAP

 

[bjackson3]

DRSteele - Entered your formulae, etc into a new worksheet, but have been getting errors in computing Q Pts. Made a sample sheet showing your work and QPTs assigned compared to what points SHOULD be assigned. Thank you in advance for looking and offering more assistance.

	A	B	C	D	E	F	G	H	I
1	Data Val:	CREDIT		HON/AP		PRE1617
2		Full		AP		NO
3		Half		HON		YES
4				NO
5
6				Full Credit Course			Half Credit Course
7	GRADE	Pre1617	Post1617	Q Pts	If Hon	If AP	Q Pts	If Hon	If AP
8	A	100	100	4	4.5	5	2	2.25	2.5
9	B	92	89	3	3.5	4	1.5	1.75	2
10	C	84	79	2	2.5	3	1	1.25	1.5
11	D	76	69	1	1.5	2	0.5	0.75	1
12	F	70	60	0	0	0	0	0	0
13
14
15	Course	Credit	Grade	Hon/AP	Pre1617	Q Pts	**Q Pts Should Equal**
16	Sample 1	Full	90	No	YES	3.00	3.00	check
17	Sample 2	Full	90	HON	YES	4.00	3.50	high by .5
18	Sample 3	Full	90	AP	YES	4.00	4.00	check
19	Sample 4	Half	90	No	YES	3.50	1.50	high by 2
20	Sample 5	Half	90	HON	YES	2.00	1.75	high by .25
21	Sample 6	Half	90	AP	YES	2.00	2.00	check
22
23	Sample 8	Full	90	No	No	4.00	4.00	check
24	Sample 9	Full	90	HON	No	5.00	4.50	high by .5
25	Sample 10	Full	90	AP	No	5.00	5.00	check
26	Sample 11	Half	90	No	No	4.50	2.00	high by 2.5
27	Sample 12	Half	90	HON	No	2.50	2.25	high by .25
28	Sample 13	Half	90	AP	No	2.50	2.50	check
29
30	Sample 15	Full	80	No	YES	2.00	2.00	check
31	Sample 16	Full	80	HON	YES	3.00	2.50	high by .5
32	Sample 17	Full	80	AP	YES	3.00	3.00	check
33	Sample 18	Half	80	No	YES	2.50	1.00	high by 1.5
34	Sample 19	Half	80	HON	YES	1.50	1.25	high by .25
35	Sample 20	Half	80	AP	YES	1.50	1.50	check
36
37	Sample 21	Full	80	No	NO	3.00	3.00	check
38	Sample 22	Full	80	HON	NO	4.00	3.50	high by .5
39	Sample 23	Full	80	AP	NO	4.00	4.00	check
40	Sample 24	Half	80	No	NO	3.50	1.50	high by 2
41	Sample 25	Half	80	HON	NO	2.00	1.75	high by .25
42	Sample 26	Half	80	AP	NO	2.00	2.00	check

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

Sheet1​

F16 and filled below formula: =INDEX($D$8:$I$12,MATCH(C16,IF(E16="yes",$B$8:$B$12,$C$8:$C$12),-1),LOOKUP(B16,$B$2:$B$3,{1;2})*LOOKUP(D16,$D$2:$D$4,{3;2;1}))

 

[DRSteele]

I'm so sorry, I made a terrible mistake. How embarrassing.

The error was in the [column number] argument in the function INDEX. This formula ought to do it.

Worksheet Formulas Cell Formula F16 =INDEX($D$8:$I$12,MATCH(C16,IF(E16="yes",$B$8:$B$12,$C$8:$C$12),-1),LOOKUP(B16,$B$2:$B$3,{0;3})+LOOKUP(D16,$D$2:$D$4,{3;2;1})) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

<strike></strike>

 

[bjackson3]

Hey, no problem about a mistake. We are all human! I'm really trying to study your formula and figure out how it is working. In using your new formula, I am still getting an error, but only with AP courses (adding copy of my sheet for you to see.) I can see what is going on with the "IF" statement, but in the LOOKUP part, what do the numbers in the curly brackets stand for/represent {3;2;1} and {0;3}? Thanks for the help - can you assist with the AP point references?

Data Val	CREDIT		HON/AP		PRE1617
	FULL		HON		NO
	HALF		AP		YES
			NO
			IF FULL CREDIT	IF HALF CREDIT
GRADE	PRE1617	Post1617	QPTS	If HON	If AP	QPTS	If HON	If AP
A	100	100	4	4.5	5	2	2.25	2.5
B	92	89	3	3.5	4	1.5	1.75	2
C	84	79	2	2.5	3	1	1.25	1.5
D	76	69	1	1.5	2	0.5	0.75	1
F	70	60	0	0	0	0	0	0
COURSE	CREDIT	GRADE	HON/AP	PRE1617	QPTS	Correct QPTS
Sample 1	FULL	90	NO	YES	3.00	3.00	check
Sample 2	FULL	90	HON	YES	3.50	3.50	check
Sample 3	FULL	90	AP	YES	3.50	4.00	low by .5
Sample 4	HALF	90	NO	YES	1.50	1.50	check
Sample 5	HALF	90	HON	YES	1.75	1.75	check
Sample 6	HALF	90	AP	YES	1.75	2.00	low by .25
Sample 8	FULL	90	NO	NO	4.00	4.00	check
Sample 9	FULL	90	HON	NO	4.50	4.50	check
Sample 10	FULL	90	AP	NO	4.50	5.00	low by .5
Sample 11	HALF	90	NO	NO	2.00	2.00	check
Sample 12	HALF	90	HON	NO	2.25	2.25	check
Sample 13	HALF	90	AP	NO	2.25	2.50	low by .25
FORMULA IN CELL F16 and filled down:
=INDEX($D$8:$I$12,MATCH(C18,IF(E18="yes",$B$8:$B$12,$C$8:$C$12),-1),LOOKUP(B18,$B$2:$B$3,{0;3})+LOOKUP(D18,$D$2:$D$4,{3;2;1}))

<colgroup><col span="2"><col span="2"><col><col span="2"><col><col span="6"></colgroup><tbody>
</tbody>

 

[DRSteele]

You've created the error by re-arranging the HON/AP data validation list: it must be in alphabetical order for function LOOKUP to report the correct result. Put it back as AP, HON, NO.

The formula LOOKUP(B18,$B$2:$B$3,{0;3}) looks up a criteria (in our case B18 is FULL) in a vector (in our case $B$2 to $B$3,) which is our list that evaluates to the array {FULL;HALF}, and determines which position the match holds (in our case 1); it then reports the first (from the 1) item in the results_vector, which is the array {0;3}, and the formula reports a 0. To this 0 we will add the result of the next LOOKUP (which is 2), the sum then being 2. That sum is the column number argument from function INDEX. So, after the other arguments to function INDEX have been evaluated, it will report the Q.PTS (which is 3.5) from the table to which it refers.

Watch these videos, which might help you understand function LOOKUP. Videos are far superior to prose in demonstrating the functionality of excel.

https://www.youtube.com/watch?v=aleaMFPk4-I
https://www.youtube.com/watch?v=HqXEcu22EaY

 

[bjackson3]

Thanks for the explanation - seems confusing but going to study this more and watch the video links you provided. Changed the list back to alphabetical but still getting QPTS for HON a half point off. Thanks again - never heard about the INDEX/VECTOR in Excel before.

 

[DRSteele]

I suspect we've got things all out of line. Here is my entire model.

	A	B	C	D	E	F	G	H	I
1	Data Validation:	CREDIT		HON/AP		PRE1617
2		Full		AP		NO
3		Half		HON		YES
4				NO
5
6				Full-Credit Course			Half-Credit Course
7	Grade	Pre1617	Post1617	Q PTS	If HON	If AP	Q PTS	If HON	If AP
8	A	100	100	4	4.5	5	2	2.25	2.5
9	B	92	89	3	3.5	4	1.5	1.75	2
10	C	84	79	2	2.5	3	1	1.25	1.5
11	D	76	69	1	1.5	2	0.5	0.75	1
12	F	70	60	0	0	0	0	0	0
13
14	RECORDS
15	COURSE	CREDIT	GRADE	HON/AP	PRE1617	Q. PTS
16	English 1	Full	82	NO	YES	2
17	English 2CP	Full	82	NO	NO	3
18	English 3H	Full	82	HON	NO	3.5
19	English Possessives	Half	75	AP	YES	1
20	English Pronouns	Half	5	NO	NO	0
21	English Spelling	Half	17	HON	YES	0
22	English Spelling	Half	90	HON	YES	1.75		BJACKSON3 manual
23	Sample 1	Full	90	No	YES	3		3
24	Sample 2	Full	90	HON	YES	3.5		3.5
25	Sample 3	Full	90	AP	YES	4		4
26	Sample 4	Half	90	No	YES	1.5		1.5
27	Sample 5	Half	90	HON	YES	1.75		1.75
28	Sample 6	Half	90	AP	YES	2		2
29	Sample 8	Full	90	No	No	4		4
30	Sample 9	Full	90	HON	No	4.5		4.5
31	Sample 10	Full	90	AP	No	5		5
32	Sample 11	Half	90	No	No	2		2
33	Sample 12	Half	90	HON	No	2.25		2.25
34	Sample 13	Half	90	AP	No	2.5		2.5
35	Sample 15	Full	80	No	YES	2		2
36	Sample 16	Full	80	HON	YES	2.5		2.5
37	Sample 17	Full	80	AP	YES	3		3
38	Sample 18	Half	80	No	YES	1		1
39	Sample 19	Half	80	HON	YES	1.25		1.25
40	Sample 20	Half	80	AP	YES	1.5		1.5
41	Sample 21	Full	80	No	NO	3		3
42	Sample 22	Full	80	HON	NO	3.5		3.5
43	Sample 23	Full	80	AP	NO	4		4
44	Sample 24	Half	80	No	NO	1.5		1.5
45	Sample 25	Half	80	HON	NO	1.75		1.75
46	Sample 26	Half	80	AP	NO	2		2

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

Sheet27​

Worksheet Formulas Cell Formula F16 =INDEX($D$8:$I$12,MATCH(C16,IF(E16="yes",$B$8:$B$12,$C$8:$C$12),-1),LOOKUP(B16,$B$2:$B$3,{0;3})+LOOKUP(D16,$D$2:$D$4,{3;2;1})) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[bjackson3]

DRSteele - I feel SO DUMB!!! The "errors" I kept getting for the HON courses were not a result of copying wrong, it was the result of not keying in the proper text string! DOH! In the data validation section at top of sheet, D1 has HON/AP. When I typed HON in cell D3, I had to include a "space bar" so the /AP wouldn't show up. Thus, when I keyed HON and DIDN'T include a "space" Excel was not ready correctly. You, my friend, are a lifesaver for having so much patience. Thank you again!