IF Formula for Class Grades

[silky939]

I am attempting to create an IF or IF(AND formula that I can use to display the letter grade I earned based off of a certain percentage criteria. I have had trouble creating this formula myself. Hopefully someone can help. Please refer to my notes below to examine the criteria. Let me know if you have any questions.

"A" D11>=93%
"A-" D11>= 90%, D11<93%
"B+" D11>=87%, D11<90%
"B" D11>=83%, D11<87%
"B-" D11>=80%, D11<83%
"C+" D11>=77%, D11<80%
"C" D11>=73%, D11<77%
"C-" D11>=70%, D11<73%
"D+" D11>=67%, D11<70%
"D" D11>=63%, D11<67%
"D-" D11>=60%, D11<63%
"F" D11<60%

 

[dave3009]

Hi and welcome to the forum

Try, where A2 holds the grade in %age form

=LOOKUP(A2, {0,0.6,0.63,0.67,0.7,0.73,0.77,0.8,0.83,0.87,0.9,0.93}, {"F","D-","D","D+","C-","C","C+","B-","B","B+","A-","A"})

 

[jai9]

Hi,

Try

=IF(A1>=93%,"A",IF(AND(A1>=90%,A1<93%),"A-",IF(AND(A1>=87%,A1<90%),"B+")))

Jai

 

[silky939]

Thank you both for you reply. Dave3009,I tried your formula and it returned an F for 97.50% so something is off. jai9, I extended your formula to include all of my criteria and it returned a #NAME? in the display box. Below is the formula I used. Can you look it over and tell me if you see any mistakes that would cause the #NAME? response? D11 is the percentage cell I am referring to.

=IF(D11>=93%,"A",IF(AND(D11>=90%,D11<93%),"A-",IF(AND(D11>=87%,D11<90%),"B+",IF(AND(D11>=83%,D11<87%),”B”,IF(AND(D11>=80%,D11<83%),”B-“,IF(AND(D11>=77%,D11<80%),”C+”,IF(AND(D11>=73%,D11<77%),”C”,IF(AND(D11>=70%,D11<73%),”C-“,IF(AND(D11>=67%,D11<70%),”D+”,IF(AND(D11>=63%,D11<67%),”D”,IF(AND(D11>=60%,D11<63%),”D-“,IF(D11<60%,”F”))))))))))))

 

[dave3009]

It worked for me so I'm not sure what was wrong. Using the IF method you don't need the AND but you may run into difficulties if you nest more than 7 and you're using Excel2003 or lower

Excel 2007
	E	F
2	97.5%	A
Sheet4

Cell Formulas
Range	Formula
F2	=LOOKUP(E2, {0,0.6,0.63,0.67,0.7,0.73,0.77,0.8,0.83,0.87,0.9,0.93}, {"F","D-","D","D+","C-","C","C+","B-","B","B+","A-","A"})

 

[jamtay317]

[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl66, width: 64"]88%[/TD]
[TD="class: xl65, width: 64"]B+[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64"]99%[/TD]
[TD="class: xl65, width: 64"]A[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64"]75%[/TD]
[TD="class: xl65, width: 64"]C [/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64"]57%[/TD]
[TD="class: xl65, width: 64"]F


[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 167"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Percentage[/TD]
[TD]Letter Grade[/TD]
[/TR]
[TR]
[TD]60%[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]60%[/TD]
[TD]D-[/TD]
[/TR]
[TR]
[TD]63%[/TD]
[TD]D [/TD]
[/TR]
[TR]
[TD]67%[/TD]
[TD]D+[/TD]
[/TR]
[TR]
[TD]70%[/TD]
[TD]C-[/TD]
[/TR]
[TR]
[TD]73%[/TD]
[TD]C [/TD]
[/TR]
[TR]
[TD]77%[/TD]
[TD]C+[/TD]
[/TR]
[TR]
[TD]80%[/TD]
[TD]B-[/TD]
[/TR]
[TR]
[TD]83%[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]87%[/TD]
[TD]B+[/TD]
[/TR]
[TR]
[TD]90%[/TD]
[TD]A-[/TD]
[/TR]
[TR]
[TD]93%[/TD]
[TD]A[/TD]
[/TR]
</tbody>[/TABLE]
I created this table and used =IFERROR(INDEX($B$4:$B$15,MATCH(D5,A4:A15)),"F")

 

[silky939]

dave3009, I was referring to the wrong cell it did work thank you very much! And to jai9, my quotations were slanted and that caused the #NAME? error. Thanks to both of you for your help!

 

[dave3009]

You could make a lookup table like this also, same thing but a little more flexible if your grading rules change

Excel 2007
	H	I
1	Limit	Grade
2	0%	F
3	60%	D-
4	63%	D
5	67%	D+
6	70%	C-
7	73%	C
8	77%	C+
9	80%	B-
10	83%	B
11	87%	B+
12	90%	A-
13	93%	A
14
15	%age	Grade
16	97.50%	A
Sheet4

Cell Formulas
Range	Formula
I16	=VLOOKUP(H16, H2:I13, 2, 1)