Please help with the following formula to return '% Completion planned' with conditions:

I have the following columns for tasks:

  • Planned start date
  • Due date
  • Planned duration; calculated as =1 + ([Due date]@row - [Planned start date]@row)
  • Days remaining or overdue; calculated as =[Due date]@row - TODAY()
  • % Completion planned; calculated as = 1 - ([Days remaining or overdue]@row / [Planned duration]@row)

I am trying to return the '% Completion planned' for tasks with the following conditions:

  1. Return 0% if tasks are planned (e.g. Today's date < planned start date)
  2. Return the % Completion planned based in the above calculation if tasks are in progress (e.g. Planned start date < Today's date < Due date)
  3. Return 100% if tasks are late (e.g. Today's >date due date)

For the above conditions I use the following IF statement:

=IF([Days remaining or overdue]@row > 0; 1 - ([Days remaining or overdue]@row / [Planned duration]@row); "100%")

The problem is this formula returns condition 2 & 3 correctly, but returns a negative % for condition 1, e.g. tasks planned (Today's date < planned start date)

Please assist with the formula so that all 3 conditions are met.

Thanks!

Tags:

Best Answer

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Try this:

    =MAX(MIN(1 - ([Days remaining or overdue]@row / [Planned duration]@row), 1), 0)

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • This worked, thank you! Do you mind explaining the logic behind this formula?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. 👍️


    First we compare the formula output to 1 (100%) and we pull the MIN. This means anything less than 100% will display, but if it goes beyond 100%, 100% is now the lowest number of the two and will be output.


    Then we take the MIN output and compare it to zero, pulling out the larger of the two numbers. If the MIN outputs any positive percentage (capped at 100% by the MIN function), then that number is greater than zero and will be output, but if the MIN outputs a negative percentage, zero becomes the greater of the two numbers and will be output by the MAX function.


    So basically we use a MIN to cap the high end at 100% and a MAX to cap the low end at 0%.


    =MAX(MIN(calculation, 1), 0)

    =MAX(MIN(calculation, 1), 0)

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!