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:
 Return 0% if tasks are planned (e.g. Today's date < planned start date)
 Return the % Completion planned based in the above calculation if tasks are in progress (e.g. Planned start date < Today's date < Due date)
 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!
Best 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!
Answers

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!

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

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!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.8K Get Help
 376 Global Discussions
 207 Industry Talk
 440 Announcements
 4.5K Ideas & Feature Requests
 139 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 284 Events
 33 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!