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 

Highlighted

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
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
Need FORMULA help please!!
marislav in Excel on
4 Replies