If(and) I believe

[JMB1001]

Hello!  Cell BA4 has a sum in it, anywhere from 0-10.  In cell BB4, I would like to turn that number into a "Grade" ie. . A,B,C,D with the following criteria.  Is this possible?  Thank you in advance!

Greater than 7 =A+

Between 5 and 6.9=A

Between 4 and 4.9 = B

Between 3 and 3.9 = C

Below 2.9 = D

[#2]

hi @JMB1001 

Suppose sum is in cell H14 you may use below formula:

=IFS(H12>6.9,"A+",H12>4.9,"A",H12>3.9,"B",H12>2.9,"C",H12<2.9,"D")

Hope it will help

Thanks 

Tauqeer

[#3]

Thank you @tauqeeracma! I now realize that when I drag the formula down to rows that have not had data input into it and the sum in cell H12 = 0 it returns A+. If the sum = 0, i'd like to leave blank. I tried adding an extra logical test for H12=0," ") but it's still returning an A+. what do you think I should do? Thank you again!

[#4]

alternatively

=IF(A1<=" ","",CHOOSE(MEDIAN(1,INT(A1)-1,6),"D","C","B","A","A","A+"))

I editted based on new request for blank

here is @tauqeeracma  version with blank added:

=IFS(H12<=" ","",H12>6.9,"A+",H12>4.9,"A",H12>3.9,"B",H12>2.9,"C",H12<2.9,"D")

[#5 · Accepted solution]

@JMB1001 

I have modified the formula now it will show blank in case of zero sum.

=IFS(H12=0,"",H12>6.9,"A+",H12>4.9,"A",H12>3.9,"B",H12>2.9,"C",H12<=2.9,"D")

Thanks

Tauqeer

[#6]

@mtarler 

Good alternative but I think in case of zero sum it will show grade D

Thanks

Tauqeer

[#7]

yea I wrote before that added request was added. and then I edit it before I saw your update. lol
btw though, if the grade is 0 may be different than being blank

[#8]

@mtarler 

Anyway, I appreciate your valuable comments.

[#9]

Thank you! All is working as it should. Thanks again and have a great day!

[#10]

@JMB1001 

As variant

=LOOKUP(BA4,{0,0.001,3,4,5,7},{"","D","C","B","A","A+"})

[#11]

@JMB1001 

Before proceeding, in reality I would use @Sergei Baklan 's solution. 

That said, my formula is

= GRADE(mark)

where 'mark' is an arbitrarily-sized array of marks.  GRADE is the name I have given to a λ-function

= LAMBDA(mrk,
    LET(
       marks, {7;5;4;3;0.01;0},
       grades,{"A+";"A";"B";"C";"D";""},
       grd, XLOOKUP(--mrk,marks,grades,"",-1),
       grd)
   )

Excel can still stretch the imagination!