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]

Good show! I'm glad we sussed it out.
https://www.youtube.com/shared?ci=C-LljWElW5U