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:
=IF([2nd Circuit Status]@row = “N/A”, 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)))))))))))))), 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🌝
