• 460K Members
• 11.5K Online
• 558K 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
Highlighted

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

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
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies