Forum Discussion
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
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
10 Replies
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!
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
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")- 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!
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
