SOLVED

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:

 

=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

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!

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

 

 

best response confirmed by JMB1001 (New Contributor)
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

@mtarler 

 

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

Thanks

Tauqeer

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

@mtarler 

 

Anyway, I appreciate your valuable comments.

 

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

@JMB1001 

As variant

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

@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!