Can you help me guys with my problem???TY in advance

[Iwant2be]

Hello guys!
Im just new here. I'm a teacher and i want to make my self comfortable with my job so i use excel to make my class records and grading sheet with ranking of my pupils. my major problem is, i want to program a condition that stating 2 true values i think i might use "and" function nesting in "if" function but i dont know how. these are my datas that i will give to you guys:

for example: The average grade of my pupils are 79, 89, 70, 65, 99, 92, 73 and i want to appear a result of:

Average grade <75= D
Average grade >75 but less than 80= AD
Average grade > 79 but less than 85 = A
Average grade >84 but less than 90= AA
Average grade >89 but less than 95= G
Average grade >95 but less than 100= HG

 

[FormR]

Welcome to the Forum!

Some of your ranges overlap in the example you gave - but maybe you can adapt this to your needs.

Excel Workbook
	A	B	C
1	Student	Avg Grade	Result
2	Student 1	75	D
3	Student 2	89	AA
4	Student 3	70	D
5	Student 4	65	D
6	Student 5	99	HG
7	Student 6	92	G
8	Student 7	73	D
Sheet1

 

[mole999]

I can't think of a way with if that will allow a mix and match where you test for AD but cross over A

something like

<75 = D
>=75 <80 = AD
>=80 <85 = A
>=85 <90 = AA
>=90 <95 = G
>=95 <=100 = HG

would do, this also allows for the pupil that achieves 100%

that could also be represented using a LOOKUP formula which would be shorter and neater

 

[MARK858]

Hmm, I can't see any overlap of ranges but I do see that neither 75 or 95 is covered.

Anyway a few options below a vlookup using a table (probably what I'd use), nested if's and FormR's lookup with a few differences in criteria to get closer to the ranges.

Please note that I have changed "Average grade >95 but less than 100= HG" to "Average grade >95 but less than 101= HG" to cover for 100%.

Excel Workbook
	A	B	C	D	E	F	G
1	56	D	D	D		0	D
2	84	A	A	A		76	AD
3	92	G	G	G		80	A
4	97	HG	HG	HG		85	AA
5	75	D	AD	D		90	G
6	79	AD	AD	AD		96	HG
7	81	A	A	A
8	85	AA	AA	AA
9	84	A	A	A
10	96	HG	HG	HG
11	76	AD	AD	AD
12	80	A	A	A
13	85	AA	AA	AA
14	90	G	G	G
15	95	G	HG	G
16	96	HG	HG	HG
17	100	HG	HG	HG
18	101			#N/A
19
20
21		LOW RANGE	HIGH RANGE
22	Average grade	0	74
23	Average grade >75 but less than 80= AD	76	79
24	Average grade > 79 but less than 85 = A	80	84
25	Average grade >84 but less than 90= AA	85	89
26	Average grade >89 but less than 95= G	90	94
27	Average grade >95 but less than 101= HG	96	100
Sheet1

 

[MARK858]

deleted as Jeanie did put in the 3rd table in my last edit of the previous post just read it wrong