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

JeanMarcKruger

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!

Paul Newcome

Try this:

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

Paul Newcome

Try this:

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

JeanMarcKruger

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

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)