SOLVED

New Contributor

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

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

# Re: If(and) I believe

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

# Re: If(and) I believe

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!

# Re: If(and) I believe

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

# Re: If(and) I believe

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

# Re: If(and) I believe

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

Thanks

Tauqeer

# Re: If(and) I believe

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

# Re: If(and) I believe

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

# Re: If(and) I believe

As variant

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

# Re: If(and) I believe

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},