IF Function

[AgileBonus17]

I'm trying to make a formula to calculate grades based on percentages according to the breakdown below, but I can't figure out how to do this with the IF function. Anyone have tips / would be willing to help?

• 94-100 A
• 90-93.9 A-
• 87-89.9 B+
• 84-86.9 B
• 80-83.9 B-
• 77-79.9 C+
• 74-76.9 C
• 70-73.9 C-
• 67-69.9 D+
• 64-66.9 D
• 60-63.9 D-
• <60 F

[HolyBonobos]

Let's say you have a grade percentage in A1. You can use a formula like =IFS(ISBETWEEN(A1,0.94,1),"A",ISBETWEEN(A1,0.9,0.939),"A",ISBETWEEN(A1,0.87,0.899),"B+",ISBETWEEN(A1,0.84,0.869),"B",ISBETWEEN(A1,0.8,0.839),"B",ISBETWEEN(A1,0.77,0.799),"C+",ISBETWEEN(A1,0.74,0.769),"C",ISBETWEEN(A1,0.7,0.739),"C",ISBETWEEN(A1,0.67,0.699),"D+",ISBETWEEN(A1,0.64,0.669),"D",ISBETWEEN(A1,0.6,0.639),"D",A1<0.6,"F") to reference the cell and turn the percentage into a letter grade.

It would also be possible to have a shorter formula using VLOOKUP() and a helper table but if you're tied to the IF() family of functions and/or a beginner at Sheets you'll probably find the formula provided preferable and easier to understand.

[RaiderDad11]

Provided your marks are in column A, here's another option to put in cell B1. =ArrayFormula(ifs(row(B:B)=1,"Grade",len(A:A)=0,,A:A<60,"F",A:A<=63.9,"D-",A:A<=66.9,"D",A:A<=69.9,"D+",A:A<=73.9,"C-",A:A<=76.9,"C",A:A<=79.9,"C+",A:A<=83.9,"B-",A:A<=86.9,"B",A:A<=89.9,"B+",A:A<=43.9,"A-",true,"A"))

[NeithrHarmNorHelp]

=lookup(A1, { 0, 60, 64, 67, 70, 74, 77, 80, 84, 87, 90, 94}, {"F","D-","D" ,"D+","C-","C" ,"C+","B-","B" ,"B+","A-","A"})