If then formula help
Hi All!
I have a column , Average Score, that has been calculated using other fields. I want to use the average score to determine if an initiative is a program or project. If the initiative’s average score is between: 5 and 9 it is considered a project and if the score is between 10 and 15, it is considered a program. I need a formula that will use that to state if it is program or project based off the average score into the cell of initiative type. Please help!
Best Answer
-
Paul Newcome ✭✭✭✭✭✭You would use something like this:
=IF(AND([Average Score]@row>= 5, [Average Score]@row<= 9), "Project", IF(AND([Average Score]@row>= 10, [Average Score]@row<= 15), "Program"))
Answers
-
Paul Newcome ✭✭✭✭✭✭
You would use something like this:
=IF(AND([Average Score]@row>= 5, [Average Score]@row<= 9), "Project", IF(AND([Average Score]@row>= 10, [Average Score]@row<= 15), "Program"))
-
Thank you Paul!!
-
Paul Newcome ✭✭✭✭✭✭
Help Article Resources
Categories
Can you split your current set up into 2 separate conditions and test it, but make \"Is not one Of\" your first condition so that the worklfow first filters out these departments and then add that Week 1 QA should be blank?<\/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":334,"urlcode":"automations","name":"Automations"}]},{"discussionID":111433,"type":"question","name":"MCQ form","excerpt":"I created an MCQ form that I need to evaluate. for example: 1 question, if the correct is answer is C, it can give one mark or zero mark. like this way created 5 questions, 6th column is the result. it needs to sum up the answer. Please help me.","snippet":"I created an MCQ form that I need to evaluate. for example: 1 question, if the correct is answer is C, it can give one mark or zero mark. like this way created 5 questions, 6th…","categoryID":322,"dateInserted":"2023-10-10T09:02:35+00:00","dateUpdated":null,"dateLastComment":"2023-10-10T11:44:55+00:00","insertUserID":168230,"insertUser":{"userID":168230,"name":"kumars23","url":"https:\/\/community.smartsheet.com\/profile\/kumars23","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-10T11:45:21+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":151203,"lastUser":{"userID":151203,"name":"Nick Korna","url":"https:\/\/community.smartsheet.com\/profile\/Nick%20Korna","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-10T13:32:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":35,"score":null,"hot":3393869850,"url":"https:\/\/community.smartsheet.com\/discussion\/111433\/mcq-form","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/111433\/mcq-form","format":"Rich","lastPost":{"discussionID":111433,"commentID":399251,"name":"Re: MCQ form","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/399251#Comment_399251","dateInserted":"2023-10-10T11:44:55+00:00","insertUserID":151203,"insertUser":{"userID":151203,"name":"Nick Korna","url":"https:\/\/community.smartsheet.com\/profile\/Nick%20Korna","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-10T13:32:33+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-10-10T11:39:23+00:00","dateAnswered":"2023-10-10T11:03:49+00:00","acceptedAnswers":[{"commentID":399248,"body":"
In this case you would change it to:<\/p>
=IF([1.Question]@row = \"C\",1,0)<\/p>
The [ ] brackets go around your column name that's being referenced.<\/p>"},{"commentID":399251,"body":"
If you had a column for each (1Q, 2Q, etc.) arranged in the way you've started, then you would use either of:<\/p>
=1Q@row + 2Q@row + 3Q@row +4Q@row +5Q@row <\/p>
=SUM([1Q]@row, [2Q]@row, [3Q]@row, [4Q]@row, [5Q]@row)<\/p>
If you want to skip this, then this should work:<\/p>
=IF([1.Question]@row = \"C\", 1, 0) + IF([2.Question]@row = \"B\", 1, 0) + IF([3.Question]@row = \"A\", 1, 0) + IF([4.Question]@row = \"B\", 1, 0) + IF([5.Question]@row = \"B\", 1, 0)<\/p>
![\"image.png\"](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/ZT2OGYKQZ49I\/image.png\")
<\/img><\/a>\n <\/div>\n<\/div>\n
<\/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":111414,"type":"question","name":"Red, Yellow, Green Formula","excerpt":"I am trying to get the Red, Yellow, Green dot formula to work with the information below. Red = If [submitted date] is in the past and [Assigned Engineer] is blank Yellow = If [Assigned Engineer] is not blank Green = If [Completed] is checked","snippet":"I am trying to get the Red, Yellow, Green dot formula to work with the information below. Red = If [submitted date] is in the past and [Assigned Engineer] is blank Yellow = If…","categoryID":322,"dateInserted":"2023-10-09T17:49:30+00:00","dateUpdated":null,"dateLastComment":"2023-10-10T13:12:43+00:00","insertUserID":148069,"insertUser":{"userID":148069,"name":"Cody_Gomez32","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Cody_Gomez32","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/avatarstock\/nEQMY42MFGXWS.png","dateLastActive":"2023-10-10T13:07:07+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":148069,"lastUser":{"userID":148069,"name":"Cody_Gomez32","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Cody_Gomez32","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/avatarstock\/nEQMY42MFGXWS.png","dateLastActive":"2023-10-10T13:07:07+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":31,"score":null,"hot":3393818533,"url":"https:\/\/community.smartsheet.com\/discussion\/111414\/red-yellow-green-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/111414\/red-yellow-green-formula","format":"Rich","lastPost":{"discussionID":111414,"commentID":399259,"name":"Re: Red, Yellow, Green Formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/399259#Comment_399259","dateInserted":"2023-10-10T13:12:43+00:00","insertUserID":148069,"insertUser":{"userID":148069,"name":"Cody_Gomez32","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Cody_Gomez32","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/avatarstock\/nEQMY42MFGXWS.png","dateLastActive":"2023-10-10T13:07:07+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-10-10T13:11:38+00:00","dateAnswered":"2023-10-09T18:19:38+00:00","acceptedAnswers":[{"commentID":399178,"body":"
