# IF Statement to find Grade Level

#### knaimi

##### New Member
Hi all!

Our school require all students to have 23 credits to graduate. For the students to be in 9th grade, they must have less than 6 credits. To be in 10th grade they must have between 6 and 11.99 credits. To be in 11th grade, they must have between 12 and 16.99 credits. To be in 12th 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 9th grade, between 7 and 13.99 to be in 10th 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.

 Column A Column B Column C Column N Column O State ID Name Grade Total Credit GradReq 10 11.75 23 10 7.5 24 12 20.5 24.5 10 10.65 23.4 11 17.05 23.8 9 7.7 24.75 12 22.5 27.5 11 20 27

<tbody>
</tbody>

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

#### craigpritchardweb

##### New Member
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

Last edited:

#### AhoyNC

##### Well-known Member
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
ABCDMNO
21011.7523
3107.524
41220.524.5
51010.6523.4
61117.0523.8
797.724.75
81222.527.5
9112027
10
11
12Look up table
1409
15610
1611.9911
171712
Sheet

#### craigpritchardweb

##### New Member

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.

Last edited:

#### knaimi

##### New Member
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

##### New Member

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

#### AhoyNC

##### Well-known Member
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
ABCDEFMNO
2101011.7523
399
4121220.524.5
5101010.6523.4
6111117.0523.8
7997.724.75
8121222.527.5
911112027
10
11
12Look up table
1409
15610
1611.9911
171712
Sheet

#### knaimi

##### New Member
This is perfect!!! Thank you very much.

#### AhoyNC

##### Well-known Member
You're welcome . Thanks for the feedback.

Replies
0
Views
276
Replies
0
Views
244
Replies
10
Views
388
Replies
1
Views
347
Replies
4
Views
714

1,129,751
Messages
5,638,150
Members
417,010
Latest member
jnuss03

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back