If / and forumula
Hi I'm trying to get a formula that returns the following grades.
High Distinction (HD) 85-100
Distinction (D) 75-84
Credit (C) 65-74
Pass (P) 50-64
Fail (F) <50
I've tried this a few different ways but keep getting the #UNPARSEABLE message. Appreciate any help with trying to get this formula to work.
=IF(AND(Percentage1 < 50), "FAIL"), IF(Percentage1 > 50, Percentage1 < 65), "Pass", IF(Percentage1 > 64, Percentage1 < 75), "CREDIT", IF(Percentage1 > 74, Percentage1 < 85), "DISTINCTION", IF(Percentage1 > 84, Percentage1 < 101), "HIGH DISTINCTION")))))
Comments
-
This may be as simple as a formatting issue. Looking at the following If/And example, can you try following bracket usage around your column name? =IF(AND([Value 1]39 > 75, [Value 2]39 > 75), "True", "False")
I'm not sure if this will work - just a suggestion!
-
Be aware that when using columns that are formatted to percentages you have to use decimal places to indicate a pecentage. I.E. =IF(Percentage1 < .5) .5 indicated 50% and 1 = 100%.
I also see a few other issues with your formula. Your first IF statement includes an AND but it doesn't seem to have 2 criterion.=IF(AND(Percentage1 < 50), "FAIL"), You aren't indicating two criterion here.
Also be aware that NESTED IF statements can't close until the end of the formula. HOWEVER your and statements should open and close within an IF.
IF(Percentage1 > 50, Percentage1 < 65), "Pass", -- the next part should have an AND statement here IF(AND(Percentage1 > 50, Percentage1 < 65), "Pass",
The others also have issues. Try this one:
=IF(Percentage1 < .5, "FAIL", IF(AND(Percentage1 > .5, Percentage1 < .65), "Pass", IF(AND(Percentage1 > .64, Percentage1 < .75), "CREDIT", IF(AND(Percentage1 > .74, Percentage1 < .85), "DISTINCTION", IF(AND(Percentage1 > .84, Percentage1 <= 1), "HIGH DISTINCTION")))))
-
Thanks to both of you. Mike that worked with a bit of an edit.
=IF(Percentage1 < 0.5, "FAIL", IF(AND(Percentage1 >= 0.5, Percentage1 <= 0.64), "PASS", IF(AND(Percentage1 >= 0.65, Percentage1 <= 0.74), "CREDIT", IF(AND(Percentage1 >= 0.75, Percentage1 <= 0.84), "DISTINCTION", IF(AND(Percentage1 >= 0.85, Percentage1 <= 1), "HIGH DISTINCTION")))))
-
Great, glad I could be of assistance.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.1K Get Help
- 414 Global Discussions
- 221 Industry Talk
- 460 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 141 Just for fun
- 58 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 299 Events
- 38 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!