sequential taske formula...Stuck PLS help

Matthew Bertucci

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 1-3 is checked then 60%

If task 1-4 is checked then 80%

If task 1-5 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))))))

Devin Lee

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"))))))

Amber Eakin

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

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.

Devin Lee

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"))))))

Amber Eakin

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))))))))))

Matthew Bertucci

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!

Matthew Bertucci

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 1-5 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. 😀