• 513K Members
• 2,789 Online
• 611K Conversations

## Formula isn't working all the time

Occasional Contributor

# Formula isn't working all the time

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

# Re: Formula isn't working all the time

@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".

# Re: Formula isn't working all the time

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

# Re: Formula isn't working all the time

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

# Re: Formula isn't working all the time

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

# Re: Formula isn't working all the time

Or use ROUND(B5,0) instead of B5

Highlighted

# Re: Formula isn't working all the time

This formula is better than IFS:
=LOOKUP(ROUND(A1,0),
{0,60,70,80,90},
{"F","D","C","B","A"})
Related Conversations
formula for data calculation
aayushman_mishra in Excel on
10 Replies
Creating A Sublist
zjohnson in Excel on
5 Replies
Excel Forumla to exclude empty cells.
ulken2019 in Excel on
6 Replies