IF Statement to find Grade Level

knaimi

New Member
Joined
Jun 23, 2013
Messages
34
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.

Please see example below:

Column A
Column B
Column C
Column N
Column O
State ID
Name
Grade
Total Credit
GradReq
1011.7523
107.524
1220.524.5
1010.6523.4
1117.0523.8
97.724.75
1222.527.5
112027

<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
Joined
Mar 14, 2017
Messages
17
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
Joined
Oct 10, 2011
Messages
4,759
Office Version
  1. 365
Platform
  1. Windows
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
1State IDNameGradeTotal CreditGradReq
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
13CreditsGrade
1409
15610
1611.9911
171712
Sheet
 

craigpritchardweb

New Member
Joined
Mar 14, 2017
Messages
17

ADVERTISEMENT

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
Joined
Jun 23, 2013
Messages
34
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
Joined
Jun 23, 2013
Messages
34

ADVERTISEMENT

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

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,759
Office Version
  1. 365
Platform
  1. Windows
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
1State IDNameGradeTotal CreditGradReq
2101011.7523
399
4121220.524.5
5101010.6523.4
6111117.0523.8
7997.724.75
8121222.527.5
911112027
10
11
12Look up table
13CreditsGrade
1409
15610
1611.9911
171712
Sheet
 

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,759
Office Version
  1. 365
Platform
  1. Windows
You're welcome . Thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top