Forum Discussion

JMB1001's avatar
JMB1001
Copper Contributor
Mar 11, 2021
Solved

If(and) I believe

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...
Formulas and Functions
  • tauqeeracma's avatar
    tauqeeracma
    Mar 11, 2021

    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

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Mar 11, 2021

JMB1001 

Before proceeding, in reality I would use SergeiBaklan '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!

Resources