sequential taske formula...Stuck PLS help
Need some help with this formula please;
If none of the tasks are checked the default should be 0%
If task 1 is checked then 20%
If task 1 and 2 is checked then 40%
If task 13 is checked then 60%
If task 14 is checked then 80%
If task 15 is check then 100%
Lastly the task must be checked sequentially so if one is missing if says “task missed”. Example if I have 1,2,4,5 check but not 3 it tells me I missed a task etc. Not sure how to go about this part of the formula.
Any help is greatly appreciated as always!!!
=IF(AND([Task Count 1 Kick Off]@row = 0, [Task Count 2 Review]@row = 0, [Task Count 3 Approval]@row = 0, [Task Count 4 Execution]@row = 0, [Task Count 5 Closeout ]@row = 0), 0, IF(AND([Task Count 1 Kick Off]@row = 1, [Task Count 2 Review]@row = 1, [Task Count 3 Approval]@row = 1, [Task Count 4 Execution]@row = 1, [Task Count 5 Closeout ]@row = 1), 1, IF(AND([Task Count 1 Kick Off]@row = 1, [Task Count 2 Review]@row = 1, [Task Count 3 Approval]@row = 1, [Task Count 4 Execution]@row = 1), 0.8, IF(AND([Gate 1 (kick off)]@row = 1, [Gate 2 (Review)]@row = 1, [Gate 3 (Approval)]@row = 1), 0.6, IF(AND([Task Count 1 Kick Off]@row = 1, [Task Count 2 Review]@row = 1), 0.4, IF([Task Count 1 Kick Off]@row = 1, 0.2))))))
Best Answers

Here is a formula for that
=IF(AND([Task1]@row = 0, [Task2]@row = 0, [Task3]@row = 0, [Task4]@row = 0, [Task5]@row = 0), 0, IF(AND([Task1]@row <> 0, [Task2]@row = 0, [Task3]@row = 0, [Task4]@row = 0, [Task5]@row = 0), 0.2, IF(AND([Task1]@row <> 0, [Task2]@row <> 0, [Task3]@row = 0, [Task4]@row = 0, [Task5]@row = 0), 0.4, IF(AND([Task1]@row <> 0, [Task2]@row <> 0, [Task3]@row <> 0, [Task4]@row = 0, [Task5]@row = 0), 0.6, IF(AND([Task1]@row <> 0, [Task2]@row <> 0, [Task3]@row <> 0, [Task4]@row <> 0, [Task5]@row = 0), 0.8, IF(AND([Task1]@row <> 0, [Task2]@row <> 0, [Task3]@row <> 0, [Task4]@row <> 0, [Task5]@row <> 0), 1, "Task missed"))))))
This would be the formula using your column names
=IF(AND([Gate 1 (kick off)]@row = 0, [Gate 2 (Review)]@row = 0, [Gate 3 (Approval)]@row = 0, [Gate 4 (Execution)]@row = 0, [Gate 5 (Close Out)]@row = 0), 0, IF(AND([Gate 1 (kick off)]@row <> 0, [Gate 2 (Review)]@row = 0, [Gate 3 (Approval)]@row = 0, [Gate 4 (Execution)]@row = 0, [Gate 5 (Close Out)]@row = 0), 0.2, IF(AND([Gate 1 (kick off)]@row <> 0, [Gate 2 (Review)]@row <> 0, [Gate 3 (Approval)]@row = 0, [Gate 4 (Execution)]@row = 0, [Gate 5 (Close Out)]@row = 0), 0.4, IF(AND([Gate 1 (kick off)]@row <> 0, [Gate 2 (Review)]@row <> 0, [Gate 3 (Approval)]@row <> 0, [Gate 4 (Execution)]@row = 0, [Gate 5 (Close Out)]@row = 0), 0.6, IF(AND([Gate 1 (kick off)]@row <> 0, [Gate 2 (Review)]@row <> 0, [Gate 3 (Approval)]@row <> 0, [Gate 4 (Execution)]@row <> 0, [Gate 5 (Close Out)]@row = 0), 0.8, IF(AND([Gate 1 (kick off)]@row <> 0, [Gate 2 (Review)]@row <> 0, [Gate 3 (Approval)]@row <> 0, [Gate 4 (Execution)]@row <> 0, [Gate 5 (Close Out)]@row <> 0), 1, "Task missed"))))))
=IF(AND([Gate 1 (kick off)]@row = 0, [Gate 2 (Review)]@row = 0, [Gate 3 (Approval)]@row = 0, [Gate 4 (Execution)]@row = 0, [Gate 5 (Close Out)]@row = 0), 0,
IF(AND([Gate 1 (kick off)]@row <> 0, [Gate 2 (Review)]@row = 0, [Gate 3 (Approval)]@row = 0, [Gate 4 (Execution)]@row = 0, [Gate 5 (Close Out)]@row = 0), 0.2,
IF(AND([Gate 1 (kick off)]@row <> 0, [Gate 2 (Review)]@row <> 0, [Gate 3 (Approval)]@row = 0, [Gate 4 (Execution)]@row = 0, [Gate 5 (Close Out)]@row = 0), 0.4,
IF(AND([Gate 1 (kick off)]@row <> 0, [Gate 2 (Review)]@row <> 0, [Gate 3 (Approval)]@row <> 0, [Gate 4 (Execution)]@row = 0, [Gate 5 (Close Out)]@row = 0), 0.6,
IF(AND([Gate 1 (kick off)]@row <> 0, [Gate 2 (Review)]@row <> 0, [Gate 3 (Approval)]@row <> 0, [Gate 4 (Execution)]@row <> 0, [Gate 5 (Close Out)]@row = 0), 0.8,
IF(AND([Gate 1 (kick off)]@row <> 0, [Gate 2 (Review)]@row <> 0, [Gate 3 (Approval)]@row <> 0, [Gate 4 (Execution)]@row <> 0, [Gate 5 (Close Out)]@row <> 0), 1, "Task missed"))))))

Using only the checkbox columns, I created a formula that would give the percentages you requested and would give a "Task Missed" for any time when not done sequentially. You would replace the numbers with the titles of your columns.
=IF(AND([2]@row = 1, [1]@row = 0), "Task Missed", IF(AND([3]@row = 1, OR([1]@row = 0, [2]@row = 0)), "Task Missed", IF(AND([4]@row = 1, OR([1]@row = 0, [2]@row = 0, [3]@row = 0)), "Task Missed", IF(AND([5]@row = 1, OR([1]@row = 0, [2]@row = 0, [4]@row = 0, [4]@row = 0)), "Task Missed", IF(AND([1]@row = 0, [2]@row = 0, [3]@row = 0, [4]@row = 0, [5]@row = 0), 0, IF(AND([1]@row = 1, [2]@row = 1, [3]@row = 1, [4]@row = 1, [5]@row = 1), 1, IF(AND([1]@row = 1, [2]@row = 1, [3]@row = 1, [4]@row = 1), 0.8, IF(AND([1]@row = 1, [2]@row = 1, [3]@row = 1), 0.6, IF(AND([1]@row = 1, [2]@row = 1), 0.4, IF([1]@row = 1, 0.2))))))))))
Amber Eakin, MSLS, M.Ed.
Adult Education Specialist  Process Improvement Enthusiast
Answers

I'm confused by the Task Count columns. It looks like they have column formulas. What are they for? That might help in understanding what's happening with your formula because I see they're included in what you created here.
Amber Eakin, MSLS, M.Ed.
Adult Education Specialist  Process Improvement Enthusiast

Here is a formula for that
=IF(AND([Task1]@row = 0, [Task2]@row = 0, [Task3]@row = 0, [Task4]@row = 0, [Task5]@row = 0), 0, IF(AND([Task1]@row <> 0, [Task2]@row = 0, [Task3]@row = 0, [Task4]@row = 0, [Task5]@row = 0), 0.2, IF(AND([Task1]@row <> 0, [Task2]@row <> 0, [Task3]@row = 0, [Task4]@row = 0, [Task5]@row = 0), 0.4, IF(AND([Task1]@row <> 0, [Task2]@row <> 0, [Task3]@row <> 0, [Task4]@row = 0, [Task5]@row = 0), 0.6, IF(AND([Task1]@row <> 0, [Task2]@row <> 0, [Task3]@row <> 0, [Task4]@row <> 0, [Task5]@row = 0), 0.8, IF(AND([Task1]@row <> 0, [Task2]@row <> 0, [Task3]@row <> 0, [Task4]@row <> 0, [Task5]@row <> 0), 1, "Task missed"))))))
This would be the formula using your column names
=IF(AND([Gate 1 (kick off)]@row = 0, [Gate 2 (Review)]@row = 0, [Gate 3 (Approval)]@row = 0, [Gate 4 (Execution)]@row = 0, [Gate 5 (Close Out)]@row = 0), 0, IF(AND([Gate 1 (kick off)]@row <> 0, [Gate 2 (Review)]@row = 0, [Gate 3 (Approval)]@row = 0, [Gate 4 (Execution)]@row = 0, [Gate 5 (Close Out)]@row = 0), 0.2, IF(AND([Gate 1 (kick off)]@row <> 0, [Gate 2 (Review)]@row <> 0, [Gate 3 (Approval)]@row = 0, [Gate 4 (Execution)]@row = 0, [Gate 5 (Close Out)]@row = 0), 0.4, IF(AND([Gate 1 (kick off)]@row <> 0, [Gate 2 (Review)]@row <> 0, [Gate 3 (Approval)]@row <> 0, [Gate 4 (Execution)]@row = 0, [Gate 5 (Close Out)]@row = 0), 0.6, IF(AND([Gate 1 (kick off)]@row <> 0, [Gate 2 (Review)]@row <> 0, [Gate 3 (Approval)]@row <> 0, [Gate 4 (Execution)]@row <> 0, [Gate 5 (Close Out)]@row = 0), 0.8, IF(AND([Gate 1 (kick off)]@row <> 0, [Gate 2 (Review)]@row <> 0, [Gate 3 (Approval)]@row <> 0, [Gate 4 (Execution)]@row <> 0, [Gate 5 (Close Out)]@row <> 0), 1, "Task missed"))))))
=IF(AND([Gate 1 (kick off)]@row = 0, [Gate 2 (Review)]@row = 0, [Gate 3 (Approval)]@row = 0, [Gate 4 (Execution)]@row = 0, [Gate 5 (Close Out)]@row = 0), 0,
IF(AND([Gate 1 (kick off)]@row <> 0, [Gate 2 (Review)]@row = 0, [Gate 3 (Approval)]@row = 0, [Gate 4 (Execution)]@row = 0, [Gate 5 (Close Out)]@row = 0), 0.2,
IF(AND([Gate 1 (kick off)]@row <> 0, [Gate 2 (Review)]@row <> 0, [Gate 3 (Approval)]@row = 0, [Gate 4 (Execution)]@row = 0, [Gate 5 (Close Out)]@row = 0), 0.4,
IF(AND([Gate 1 (kick off)]@row <> 0, [Gate 2 (Review)]@row <> 0, [Gate 3 (Approval)]@row <> 0, [Gate 4 (Execution)]@row = 0, [Gate 5 (Close Out)]@row = 0), 0.6,
IF(AND([Gate 1 (kick off)]@row <> 0, [Gate 2 (Review)]@row <> 0, [Gate 3 (Approval)]@row <> 0, [Gate 4 (Execution)]@row <> 0, [Gate 5 (Close Out)]@row = 0), 0.8,
IF(AND([Gate 1 (kick off)]@row <> 0, [Gate 2 (Review)]@row <> 0, [Gate 3 (Approval)]@row <> 0, [Gate 4 (Execution)]@row <> 0, [Gate 5 (Close Out)]@row <> 0), 1, "Task missed"))))))

Using only the checkbox columns, I created a formula that would give the percentages you requested and would give a "Task Missed" for any time when not done sequentially. You would replace the numbers with the titles of your columns.
=IF(AND([2]@row = 1, [1]@row = 0), "Task Missed", IF(AND([3]@row = 1, OR([1]@row = 0, [2]@row = 0)), "Task Missed", IF(AND([4]@row = 1, OR([1]@row = 0, [2]@row = 0, [3]@row = 0)), "Task Missed", IF(AND([5]@row = 1, OR([1]@row = 0, [2]@row = 0, [4]@row = 0, [4]@row = 0)), "Task Missed", IF(AND([1]@row = 0, [2]@row = 0, [3]@row = 0, [4]@row = 0, [5]@row = 0), 0, IF(AND([1]@row = 1, [2]@row = 1, [3]@row = 1, [4]@row = 1, [5]@row = 1), 1, IF(AND([1]@row = 1, [2]@row = 1, [3]@row = 1, [4]@row = 1), 0.8, IF(AND([1]@row = 1, [2]@row = 1, [3]@row = 1), 0.6, IF(AND([1]@row = 1, [2]@row = 1), 0.4, IF([1]@row = 1, 0.2))))))))))
Amber Eakin, MSLS, M.Ed.
Adult Education Specialist  Process Improvement Enthusiast

Thank you for both (Amber and Devin) for responding so quickly. That solved my problem. I really appreciate the Smartsheet community as everyone is very helpful and responsive.
Much appreciated!

I'm confused by the Task Count columns. It looks like they have column formulas. What are they for? That might help in understanding what's happening with your formula because I see they're included in what you created here.
The Task Count columns are helper colums signifying if a Gate Stage checkbox is checked or not. So, I was trying to say if Gates 15 are checked in order it would show the % complete progressing but if one box was missed it flagged it.
Does the help?
In my haste I wasn't a clear as I could have been. 😀
Help Article Resources
Categories
Check out the Formula Handbook template!