TASK % COMPLETE FORMULA
I created a column to be provide me the task (row) total % complete.
The two columns below have a dropdown status that each represent a % complete
The % for each status is below:
=如果([第二电路状态]@row =“N / A”,如果(或([1 Circuit Status]@row = “Waiting to sign the contract”, [1st Circuit Status]@row = “Waiting for Quote”, [1st Circuit Status]@row = “Waiting for Order Validation to complete”, [1st Circuit Status]@row = “Cancelled”, [1st Circuit Status]@row = “Requirement Review”, [1st Circuit Status]@row = “Not Started”, [1st Circuit Status]@row = “Out of Scope”), 0, IF([1st Circuit Status]@row = “Started”, .01, IF([1st Circuit Status]@row = “Waiting to assign to local carrier”, .05, IF([1st Circuit Status]@row = “Circuit Order in process”, .04, IF([1st Circuit Status]@row = “Need to reassign to another Carrier”, .05, IF([1st Circuit Status]@row = “Assigned to Local Carrier”, .1, IF([1st Circuit Status]@row = “Circuit in Design Phase”, .6, IF([1st Circuit Status]@row = “Circuit Site Survey in Progress”, .65, IF([1st Circuit Status]@row = “Circuit Site Survey Complete”, .70, IF([1st Circuit Status]@row = “LCON Waiting Carrier to provide Design”, .71, IF([1st Circuit Status]@row = “On Hold for Client Financial Approval to start Construction/Site Survey”, .72, IF([1st Circuit Status]@row = “Waiting for LCON to Approve Site Survey”, .73, IF([1st Circuit Status]@row = “Circuit in Construction Phase”, .8, IF([1st Circuit Status]@row = “Circuit installed waiting carrier to deliver”, .99, IF(OR([1st Circuit Status]@row = “Circuit Delivered”, [1st Circuit Status]@row = “Use local Current Circuits”), 1)))))))))))))), AVG(IF(OR([1st Circuit Status]@row = “Waiting to sign the contract”, [1st Circuit Status]@row = “Waiting for Quote”, [1st Circuit Status]@row = “Waiting for Order Validation to complete”, [1st Circuit Status]@row = “Cancelled”, [1st Circuit Status]@row = “Requirement Review”, [1st Circuit Status]@row = “Not Started”, [1st Circuit Status]@row = “Out of Scope”), 0, IF([1st Circuit Status]@row = “Started”, .01, IF([1st Circuit Status]@row = “Waiting to assign to local carrier”, .05, IF([1st Circuit Status]@row = “Circuit Order in process”, .04, IF([1st Circuit Status]@row = “Need to reassign to another Carrier”, .05, IF([1st Circuit Status]@row = “Assigned to Local Carrier”, .1, IF([1st Circuit Status]@row = “Circuit in Design Phase”, .6, IF([1st Circuit Status]@row = “Circuit Site Survey in Progress”, .65, IF([1st Circuit Status]@row = “Circuit Site Survey Complete”, .70, IF([1st Circuit Status]@row = “LCON Waiting Carrier to provide Design”, .71, IF([1st Circuit Status]@row = “On Hold for Client Financial Approval to start Construction/Site Survey”, .72, IF([1st Circuit Status]@row = “Waiting for LCON to Approve Site Survey”, .73, IF([1st Circuit Status]@row = “Circuit in Construction Phase”, .8, IF([1st Circuit Status]@row = “Circuit installed waiting carrier to deliver”, .99, IF(OR([1st Circuit Status]@row = “Circuit Delivered”, [1st Circuit Status]@row = “Use local Current Circuits”), 1)))))))))))))), IF(OR([2nd Circuit Status]@row = “Waiting to sign the contract”, [2nd Circuit Status]@row = “Waiting for Quote”, [2nd Circuit Status]@row = “Waiting for Order Validation to complete”, [2nd Circuit Status]@row = “Cancelled”, [2nd Circuit Status]@row = “Requirement Review”, [2nd Circuit Status]@row = “Not Started”, [2nd Circuit Status]@row = “Out of Scope”), 0, IF([2nd Circuit Status]@row = “Started”, .01, IF([2nd Circuit Status]@row = “Waiting to assign to local carrier”, .05, IF([2nd Circuit Status]@row = “Circuit Order in process”, .04, IF([2nd Circuit Status]@row = “Need to reassign to another Carrier”, .05, IF([2nd Circuit Status]@row = “Assigned to Local Carrier”, .1, IF([2nd Circuit Status]@row = “Circuit in Design Phase”, .6, IF([2nd Circuit Status]@row = “Circuit Site Survey in Progress”, .65, IF([2nd Circuit Status]@row = “Circuit Site Survey Complete”, .70, IF([2nd Circuit Status]@row = “LCON Waiting Carrier to provide Design”, .71, IF([2nd Circuit Status]@row = “On Hold for Client Financial Approval to start Construction/Site Survey”, .72, IF([2nd Circuit Status]@row = “Waiting for LCON to Approve Site Survey”, .73, IF([2nd Circuit Status]@row = “Circuit in Construction Phase”, .8, IF([2nd Circuit Status]@row = “Circuit installed waiting carrier to deliver”, .99, IF(OR([2nd Circuit Status]@row = “Circuit Delivered”, [2nd Circuit Status]@row = “Use local Current Circuits”), 1))))))))))))))))
The formula I was trying to use, the cell didn't accept because it was too big. If someone can help me to make this formula shorter I really appreciate that.
Rob
Answers
-
L_123 ✭✭✭✭✭✭I would have a separate sheet with your table, then use index match to return the correct associated values
-
RobNY2 ✭
L_123
I don't know what you are saying. The table above is just to show the values.
Rob
-
L_123 ✭✭✭✭✭✭
put the table into another sheet, 2 columns (A is the name, B is the value)
=IF([2nd Circuit Status]@row = “N/A”,index({B},match([1st Circuit Status]@row ,{A},0)),index({B},match([2nd Circuit Status]@row ,{A},0
-
RobNY2 ✭
L_123
I added this formula:
=IF([2nd Circuit Status]@row = “N/A”, index({Values},match([1st Circuit Status]@row ,{Name},0)),index({Values},match([2nd Circuit Status]@row,{Name},0
Values is the name for the sheet column B
Name is the name for the sheet column A
and I am getting Unparseable
Rob
-
L_123 ✭✭✭✭✭✭
Did you put the table on the other sheet, and set up the other sheet column references?
-
RobNY2 ✭
L_123
YES!
=IF([2nd Circuit Status]@row = “N/A”, index({"Values" is the sheet name and column},match([1st Circuit Status]@row ,{"Name" is the sheet name and column},0)),index({Values},match([2nd Circuit Status]@row,{Name},0
Values is the name for the sheet column B
Name is the name for the sheet column A
Rob
-
RobNY2 ✭
Mike
Repeating what I wrote based what L_123 suggested:
I created another sheet and names the column B and "Values" and I named the column A as "Name" as it was suggested above but I still get Unparseable
=IF([2nd Circuit Status]@row = “N/A”, index({Values},match([1st Circuit Status]@row ,{Name},0)),index({Values},match([2nd Circuit Status]@row,{Name},0
The values and Names are above and I am stuck on this formula
If you can help me with this too I really appreciated
Rob
Help Article Resources
Categories
Check out theFormula Handbook template!
Instead of applying the formula to \"Multiselect Text String\" row, did you tried with \"Multiselect Values\" row?<\/p>
=IF(HAS([Multiselect Values]@row, [Component ID]@row), \"MATCH\", \"NO MATCH\")<\/p>
Thank you,<\/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":109493,"type":"question","name":"I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","excerpt":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5 different statuses in a separate column. So using the screenshot I've provided as an example (although it doesn't have 5 different statuses in the…","snippet":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5…","categoryID":322,"dateInserted":"2023-08-25T20:03:21+00:00","dateUpdated":null,"dateLastComment":"2023-08-26T00:34:49+00:00","insertUserID":165710,"insertUser":{"userID":165710,"name":"SmarsheetNewb","url":"https:\/\/community.smartsheet.com\/profile\/SmarsheetNewb","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-26T00:33:27+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":161714,"lastUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-27T02:16:35+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":24,"score":null,"hot":3386005690,"url":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109493,"commentID":392692,"name":"Re: I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392692#Comment_392692","dateInserted":"2023-08-26T00:34:49+00:00","insertUserID":161714,"insertUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-27T02:16:35+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-08-26T00:33:25+00:00","dateAnswered":"2023-08-25T20:44:12+00:00","acceptedAnswers":[{"commentID":392662,"body":"
Try this:<\/p>
=COUNTIFS([Item Number]:[Item Number], OR(@cell = \"C001\", @cell = \"COO2\", @cell = \"COO3\", @cell = \"COO4\"), [Status]:[Status], OR(@cell = \"Green\", @cell = \"Yellow\", @cell = \"Red\"))<\/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":109474,"type":"question","name":"Help with date calculation formula","excerpt":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the resolution date is blank I want it to use the current date in the calculation to see how long this issue has gone unresolved. Any help is much…","snippet":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the…","categoryID":322,"dateInserted":"2023-08-25T16:29:39+00:00","dateUpdated":"2023-08-25T16:29:59+00:00","dateLastComment":"2023-08-25T23:01:30+00:00","insertUserID":165688,"insertUser":{"userID":165688,"name":"Nwest","title":"Systems Analyst","url":"https:\/\/community.smartsheet.com\/profile\/Nwest","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ukHVZ18ImX4!BcjWAe8S9SY!l7iQo_PZHOx","dateLastActive":"2023-08-25T17:22:30+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":165688,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":24,"score":null,"hot":3385987269,"url":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109474,"commentID":392687,"name":"Re: Help with date calculation formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392687#Comment_392687","dateInserted":"2023-08-25T23:01:30+00:00","insertUserID":8888,"insertUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06: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-08-25T17:04:22+00:00","dateAnswered":"2023-08-25T16:36:59+00:00","acceptedAnswers":[{"commentID":392622,"body":"
