IF Statement to find Grade Level

[knaimi]

Hi all!

Our school require all students to have 23 credits to graduate. For the students to be in 9^th grade, they must have less than 6 credits. To be in 10^th grade they must have between 6 and 11.99 credits. To be in 11^th grade, they must have between 12 and 16.99 credits. To be in 12^th grade, they must have 17 or more credits.

The problem is when we receive transferred students who have attended other high schools, their graduation requirement might be higher. So if the transferred student needs 24 credits to graduate then she must have less than 7 credits to be in 9^th grade, between 7 and 13.99 to be in 10^th grade …etc.

I need to have an if statement to place all students in the right grade level. In excel 2016 sheet I have 17 columns: Column C has the grade level, column N has the total credits the students earned so far, column O has Graduation Requirement (23 or higher). I need to place the If statement in column C to show the correct grade level.

Please see example below:

[TABLE="width: 468"]
<tbody>[TR]
[TD]Column A
[/TD]
[TD]Column B
[/TD]
[TD]Column C
[/TD]
[TD][/TD]
[TD]Column N
[/TD]
[TD]Column O[/TD]
[/TR]
[TR]
[TD]State ID
[/TD]
[TD]Name
[/TD]
[TD]Grade
[/TD]
[TD][/TD]
[TD]Total Credit
[/TD]
[TD]GradReq[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]10[/TD]
[TD][/TD]
[TD]11.75[/TD]
[TD]23[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]10[/TD]
[TD][/TD]
[TD]7.5[/TD]
[TD]24[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]12[/TD]
[TD][/TD]
[TD]20.5[/TD]
[TD]24.5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]10[/TD]
[TD][/TD]
[TD]10.65[/TD]
[TD]23.4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]11[/TD]
[TD][/TD]
[TD]17.05[/TD]
[TD]23.8[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]9[/TD]
[TD][/TD]
[TD]7.7[/TD]
[TD]24.75[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]12[/TD]
[TD][/TD]
[TD]22.5[/TD]
[TD]27.5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]11[/TD]
[TD][/TD]
[TD]20[/TD]
[TD]27[/TD]
[/TR]
</tbody>[/TABLE]

 

[craigpritchardweb]

Do you know VLOOKUP? I would imagine that is a much better (and elegant) solution than having a huge IF statement.

If you don't know VLOOKUP, perhaps I (or someone else) can help but if you do, maybe this will jog your memory

 

[knaimi]

Whatever works out. Could you please help with VLOOKUP?

 

[AhoyNC]

Maybe something like this:
Set up a look up table somewhere in your workbook as in cells A13 to B17 in the example below.
Then use the LOOKUP function in cell C2 and copy down column as needed.
Change ranges to match your data and cells that contain your look up table.

Excel Workbook
	A	B	C	D	M	N	O
1	State ID	Name	Grade			Total Credit	GradReq
2			10			11.75	23
3			10			7.5	24
4			12			20.5	24.5
5			10			10.65	23.4
6			11			17.05	23.8
7			9			7.7	24.75
8			12			22.5	27.5
9			11			20	27
10
11
12	Look up table
13	Credits	Grade
14	0	9
15	6	10
16	11.99	11
17	17	12
Sheet

 

[craigpritchardweb]

AhoyNC's solution is fairly similar to mine, so you might as well use his solution.

You could also set up the lookup table on another worksheet. Just make sure you reference the other worksheet correctly if you do it that way

When setting up a lookup table, it is important to start from the lowest value (in this case, the lowest possible mark) and work up, otherwise the lookup function will not work as intended.

 

[knaimi]

It did work. Thanks.

The only concern is that when when any cell in N and O columns were blank, the student was placed in 12th grade. How can I fix it where when those two cells are blank, the student should be placed in 9th grade?

Thanks,

 

[knaimi]

Also, how can I hide and protect the table so it won't be accidentally deleted?

 

[AhoyNC]

Try:
Also, if you don't want to put a look up table in your workbook you could ud=se the formula in cell E2 of the example.

Excel Workbook
	A	B	C	D	E	F	M	N	O
1	State ID	Name	Grade					Total Credit	GradReq
2			10		10			11.75	23
3			9		9
4			12		12			20.5	24.5
5			10		10			10.65	23.4
6			11		11			17.05	23.8
7			9		9			7.7	24.75
8			12		12			22.5	27.5
9			11		11			20	27
10
11
12	Look up table
13	Credits	Grade
14	0	9
15	6	10
16	11.99	11
17	17	12
Sheet

 

[knaimi]

This is perfect!!! Thank you very much.

 

[AhoyNC]

You're welcome . Thanks for the feedback.