TASK % COMPLETE FORMULA

RobNY2

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🌝

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