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
-
Dartwohl ✭✭✭✭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!
-
Mike Wilday ✭✭✭✭✭✭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")))))
-
Mike Wilday ✭✭✭✭✭✭
Great, glad I could be of assistance.
Help Article Resources
Categories
Sounds like you just need a basic COUNTIFS.<\/p>
=COUNTIFS([Column Name]:[Column name], @cell = \"Yes\")<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]},{"discussionID":107618,"type":"question","name":"Formula","excerpt":"I am new ti Smartsheet and formulas. This is the formula I have, and it is not working, and I don't know what I have done wrong. =COUNTIFS({Stage 6}, =\"6- Testing\", [{Stage 61}, =\"6- Opening\"]) What I am trying to get is the number of projects that are in phase 6 weather it is opening or testing. Both are values in the…","snippet":"I am new ti Smartsheet and formulas. This is the formula I have, and it is not working, and I don't know what I have done wrong. =COUNTIFS({Stage 6}, =\"6- Testing\", [{Stage 61},…","categoryID":322,"dateInserted":"2023-07-14T14:02:17+00:00","dateUpdated":null,"dateLastComment":"2023-07-14T14:17:16+00:00","insertUserID":162539,"insertUser":{"userID":162539,"name":"L. Sauerhoff","title":"Product Manager","url":"https:\/\/community.smartsheet.com\/profile\/L.%20Sauerhoff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-14T15:09:42+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-07-14T16:12:14+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":17,"score":null,"hot":3378688773,"url":"https:\/\/community.smartsheet.com\/discussion\/107618\/formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107618\/formula","format":"Rich","lastPost":{"discussionID":107618,"commentID":385280,"name":"Re: Formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/385280#Comment_385280","dateInserted":"2023-07-14T14:17:16+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-07-14T16:12:14+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-14T15:09:39+00:00","dateAnswered":"2023-07-14T14:12:52+00:00","acceptedAnswers":[{"commentID":385276,"body":"
Give this a try:<\/p>
=COUNTIFS({Stage 6}, OR(@cell = \"6- Testing\", @cell =\"6- Opening\"))<\/p>"},{"commentID":385280,"body":"
Your syntax is a bit off to start. You don't need the square brackets around the second range\/criteria set.<\/p>
=COUNTIFS({Stage 6}, =\"6- Testing\", {Stage 61}, =\"6- Opening\")<\/p>
<\/p>
I also notice that you said they are both in the same column yet you have two different ranges listed in your formula (6 and 61).<\/p>
<\/p>
Even after correcting the syntax and range issue though, it still will not accomplish what you are wanting because the COUNTIFS implies \"and\" when looking at multiple range\/criteria sets. This means you are technically counting rows where the same cell is equal to both \"6- Testing\" AND \"6- Opening\" at the same time which is not possible.<\/p>
<\/p>
What you really want is to count cells within the same range that are either \"6- Testing\" OR \"6- Opening\", and that looks more like:<\/p>
=COUNTIFS({Range}, OR(@cell = \"6- Testing\", @cell = \"6- Opening\"))<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":107579,"type":"question","name":"Unable to convert a vlookup formula into values","excerpt":"I have multiple columns with column formulas built that do a lookup into another Smartsheet. Want to convert the lookup values into values in the workbook that I built the formulas in, and then delete the workbook that is currently being referenced to find lookup. I cannot figure out how to do it. I do not see an option to…","snippet":"I have multiple columns with column formulas built that do a lookup into another Smartsheet. Want to convert the lookup values into values in the workbook that I built the…","categoryID":322,"dateInserted":"2023-07-13T19:16:39+00:00","dateUpdated":"2023-07-13T19:43:47+00:00","dateLastComment":"2023-07-14T11:41:08+00:00","insertUserID":163469,"insertUser":{"userID":163469,"name":"Rod H","title":"Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/Rod%20H","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-14T11:11:49+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":91566,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-07-14T16:12:14+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":15,"countViews":55,"score":null,"hot":3378619667,"url":"https:\/\/community.smartsheet.com\/discussion\/107579\/unable-to-convert-a-vlookup-formula-into-values","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107579\/unable-to-convert-a-vlookup-formula-into-values","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":107579,"commentID":385239,"name":"Re: Unable to convert a vlookup formula into values","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/385239#Comment_385239","dateInserted":"2023-07-14T11:41:08+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-07-14T16:12:14+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-14T11:11:48+00:00","dateAnswered":"2023-07-14T10:45:29+00:00","acceptedAnswers":[{"commentID":385232,"body":"
A Big Thank You! to both Paul and Itai! Yes you have the answer. Deeply appreciated.<\/p>
Rod<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">
