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!