# sequential taske formula...Stuck PLS help

Options
✭✭✭✭

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

• ✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭
Options

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!

• ✭✭✭✭
Options

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!