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

=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

  • Devin Lee
    Devin Lee ✭✭✭✭✭
    Answer ✓
    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"))))))

  • Amber Eakin
    Amber Eakin ✭✭✭✭✭✭
    Answer ✓
    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

Answers

  • Amber Eakin
    Amber Eakin ✭✭✭✭✭✭
    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

  • Devin Lee
    Devin Lee ✭✭✭✭✭
    Answer ✓
    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"))))))

  • Amber Eakin
    Amber Eakin ✭✭✭✭✭✭
    Answer ✓
    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

  • Matthew Bertucci
    Matthew Bertucci ✭✭✭✭
    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!

  • Matthew Bertucci
    Matthew Bertucci ✭✭✭✭
    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!