If(and) I believe

New Contributor

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

10 Replies

hi @JMB1001 


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




Hope it will help



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!




=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")



best response confirmed by JMB1001 (New Contributor)



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







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



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



Anyway, I appreciate your valuable comments.


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


As variant



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,
       marks, {7;5;4;3;0.01;0},
       grd, XLOOKUP(--mrk,marks,grades,"",-1),

Excel can still stretch the imagination!