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

@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

Highlighted
This formula is better than IFS:
=LOOKUP(ROUND(A1,0),
{0,60,70,80,90},
{"F","D","C","B","A"})
Related Conversations
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
2 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
202 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies