Mar 11 2021 10:28 AM
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
Mar 11 2021 10:38 AM
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
Mar 11 2021 10:50 AM
Mar 11 2021 10:58 AM - edited Mar 11 2021 11:03 AM
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")
Mar 11 2021 11:02 AM
Solution
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
Mar 11 2021 11:03 AM
Mar 11 2021 11:05 AM
Mar 11 2021 11:11 AM
Mar 11 2021 12:34 PM
Mar 11 2021 01:53 PM
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!
Mar 11 2021 11:02 AM
Solution
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