Home

Formula isn't working all the time

%3CLINGO-SUB%20id%3D%22lingo-sub-408926%22%20slang%3D%22en-US%22%3EFormula%20isn't%20working%20all%20the%20time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-408926%22%20slang%3D%22en-US%22%3E%3CP%3EThis%20formula%20examines%20a%20number%20and%20outputs%20the%20corresponding%20letter%20grade%20it%20works%20on%20every%20class%20grade%20except%20%3CSTRONG%3EB4%26nbsp%3B%3C%2FSTRONG%3Ewhere%20it%20says%20the%20letter%20grade%20is%20%22D%22%20when%20I%20have%20a%2070.%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIFS(B5%26gt%3B%3D90%2C%22A%22%2CB5%26gt%3B%3D80%2C%22B%22%2CB5%26gt%3B%3D70%2C%22C%22%2CB5%26gt%3B%3D60%2C%22D%22%2CB5%26lt%3B60%2C%22F%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%3CSTRONG%3E%20A%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20B%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E1%26nbsp%3B%3C%2FSTRONG%3E%20%26nbsp%3B98%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BA%3C%2FP%3E%3CP%3E%3CSTRONG%3E2%3C%2FSTRONG%3E%26nbsp%3B%20%26nbsp%3B70%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BC%3C%2FP%3E%3CP%3E%3CSTRONG%3E3%26nbsp%3B%3C%2FSTRONG%3E%20%26nbsp%3B80%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BB%3C%2FP%3E%3CP%3E%3CSTRONG%3E4%26nbsp%3B%3C%2FSTRONG%3E%20%26nbsp%3B70%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BD%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-408926%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-409043%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20isn't%20working%20all%20the%20time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-409043%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F159010%22%20target%3D%22_blank%22%3E%40Hayden%20Alston%3C%2FA%3E%26nbsp%3Bdoes%20column%26nbsp%3BA%20have%20any%20rounding%20going%20on%3F%26nbsp%3B%20If%20a%2069.9%20is%20displayed%20as%20a%2070%2C%20it%20would%20evaluate%20as%20a%20%22D%22.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-409175%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20isn't%20working%20all%20the%20time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-409175%22%20slang%3D%22en-US%22%3E%3CP%3Ethat%20was%20the%20problem%20i%20set%20the%20grades%20to%20be%20two%20decimals%20and%20the%20number%20with%20more%20that%20two%20decimals%20is%2069.997%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F313753%22%20target%3D%22_blank%22%3E%40rdwyer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-409280%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20isn't%20working%20all%20the%20time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-409280%22%20slang%3D%22en-US%22%3E%3CP%3Ei%20just%20made%20the%20numbers%20half%20a%20point%20lower%20to%20include%20the%20rounding%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F313753%22%20target%3D%22_blank%22%3E%40rdwyer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-409342%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20isn't%20working%20all%20the%20time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-409342%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F159010%22%20target%3D%22_blank%22%3E%40Hayden%20Alston%3C%2FA%3E%26nbsp%3BYou%20can%20add%20another%20column%20and%20use%20the%20ROUND%20function%20to%20handle%20this%2C%20then%20evaluate%20the%20rounded%20numbers%20for%20letter%20grades.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-409379%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20isn't%20working%20all%20the%20time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-409379%22%20slang%3D%22en-US%22%3E%3CP%3EOr%20use%26nbsp%3BROUND(B5%2C0)%20instead%20of%20B5%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-413907%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20isn't%20working%20all%20the%20time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-413907%22%20slang%3D%22en-US%22%3EThis%20formula%20is%20better%20than%20IFS%3A%3CBR%20%2F%3E%3DLOOKUP(ROUND(A1%2C0)%2C%3CBR%20%2F%3E%7B0%2C60%2C70%2C80%2C90%7D%2C%3CBR%20%2F%3E%7B%22F%22%2C%22D%22%2C%22C%22%2C%22B%22%2C%22A%22%7D)%3C%2FLINGO-BODY%3E
Hayden Alston
Occasional 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
Highlighted

@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"})
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