Formula isn't working all the time

Copper Contributor

This formula examines a number and outputs the corresponding letter grade it works on every class grade except B4 where it says the letter grade is "D" when I have a 70. 

=IFS(B5>=90,"A",B5>=80,"B",B5>=70,"C",B5>=60,"D",B5<60,"F")

      A      B

 98     A

2   70     C

 80     B

 70     D

 

6 Replies

@Hayden Alston does column A have any rounding going on?  If a 69.9 is displayed as a 70, it would evaluate as a "D".

that was the problem i set the grades to be two decimals and the number with more that two decimals is 69.997@rdwyer 

i just made the numbers half a point lower to include the rounding @rdwyer 

@Hayden Alston You can add another column and use the ROUND function to handle this, then evaluate the rounded numbers for letter grades.

Or use ROUND(B5,0) instead of B5

This formula is better than IFS:
=LOOKUP(ROUND(A1,0),
{0,60,70,80,90},
{"F","D","C","B","A"})