# 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 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!!!

• 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(@row = 1, @row = 0), "Task Missed", IF(AND(@row = 1, OR(@row = 0, @row = 0)), "Task Missed", IF(AND(@row = 1, OR(@row = 0, @row = 0, @row = 0)), "Task Missed", IF(AND(@row = 1, OR(@row = 0, @row = 0, @row = 0, @row = 0)), "Task Missed", IF(AND(@row = 0, @row = 0, @row = 0, @row = 0, @row = 0), 0, IF(AND(@row = 1, @row = 1, @row = 1, @row = 1, @row = 1), 1, IF(AND(@row = 1, @row = 1, @row = 1, @row = 1), 0.8, IF(AND(@row = 1, @row = 1, @row = 1), 0.6, IF(AND(@row = 1, @row = 1), 0.4, IF(@row = 1, 0.2))))))))))

Amber Eakin, MSLS, M.Ed.

Adult Education Specialist | Process Improvement Enthusiast

• 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(@row = 1, @row = 0), "Task Missed", IF(AND(@row = 1, OR(@row = 0, @row = 0)), "Task Missed", IF(AND(@row = 1, OR(@row = 0, @row = 0, @row = 0)), "Task Missed", IF(AND(@row = 1, OR(@row = 0, @row = 0, @row = 0, @row = 0)), "Task Missed", IF(AND(@row = 0, @row = 0, @row = 0, @row = 0, @row = 0), 0, IF(AND(@row = 1, @row = 1, @row = 1, @row = 1, @row = 1), 1, IF(AND(@row = 1, @row = 1, @row = 1, @row = 1), 0.8, IF(AND(@row = 1, @row = 1, @row = 1), 0.6, IF(AND(@row = 1, @row = 1), 0.4, IF(@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 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. 😀

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!