Updating a status column based on Values in Expected % Complete and % Complete
I have dates from two columns, Expected % Complete, and % Complete which I want to use, and populate the Status Column (Dropdown) using Formulas:
The Dropdown options are " Not Started", "Behind", "On Track", and "Complete". I tried using the below formula
=IF(AND([% Complete]@row = "100%", ([Expected % Complete]@row < "100%")),"Complete", IF(AND([% Complete]@row >= [Expected % Complete]@row, ([% Complete]@row > "0%")), "On
Track", IF(AND([% Complete]@row < [Expected % Complete]@row, ([% Complete]@row > "0%")),
"Behind", "Not Started")))
How to fix this ?
Answers

Hi Mohammed,
The structure of this is great, welldone! However, when referencing a number column that has the % symbol turned on, you want to reference percentages as decimals.
For example, instead of saying "100%", you would write the number 1.
Try this:
=IF(AND([% Complete]@row = 1, ([Expected % Complete]@row < 1)), "Complete", IF(AND([% Complete]@row >= [Expected % Complete]@row, ([% Complete]@row > 0)), "On Track", IF(AND([% Complete]@row < [Expected % Complete]@row, ([% Complete]@row > 0)), "Behind", "Not Started")))
You can read more about this on our Formula FAQ page (click here).
Cheers!
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
Hi Genevieve,
I did try the above solution, and it worked. Thanks for your help.
However, I added another condition, where the status becomes "Not Started/Behind".
=IF([% Complete]@row = 1, ([Expected % Complete]@row < 1)), "Complete", IF(AND([% Complete]@row >= [Expected % Complete]@row, ([% Complete]@row > 0)), "On Track", IF(AND([% Complete]@row < [Expected % Complete]@row, ([% Complete]@row > 0)), "Behind", IF(AND([% Complete]@row < [Expected % Complete]@row, ([% Complete]@row = 0)), "Not Started/Behind", "Not Started"))).
This additional condition " IF(AND([% Complete]@row < [Expected % Complete]@row, ([% Complete]@row = 0)), "Not Started/Behind" " is to show items, that are supposed to start but are behind, as the Expected % Complete > 0, and % Complete = 0.
However, when I add this formula, the "Not Started" status doesn't populate as it should, when the Expected % Complete = 0, % Complete = 0. Instead the "Not Started/Behind" populates in the cell.
How to fix this ?
Thanks,

Hi Mohammed,
This formula has a number of extra closing parentheses that should not be there. When you use "AND" you don't need to add more parenthesis to close of different instructions. Additionally, when you "nest" IF statements together in a row, leave the last parenthesis to be closed off at the very end of the entire formula.
Try this instead:
=IF(AND([% Complete]@row = 1, [Expected % Complete]@row < 1), "Complete", IF(AND([% Complete]@row >= [Expected % Complete]@row, [% Complete]@row > 0), "On Track", IF(AND([% Complete]@row < [Expected % Complete]@row, [% Complete]@row > 0), "Behind", IF(AND([% Complete]@row < [Expected % Complete]@row, [% Complete]@row = 0), "Not Started/Behind", "Not Started"))))
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
@Genevieve P. I tried this formula, and When I have a task that is Completed, showing 100% in the % complete, and the expected completion is also 100%, it returns "On Track" in the status column, vs "Complete", any suggestions for correcting this?

Hi @Amy D
Great catch! You'll want to add in a statement that says if they're both 1, return "Complete".
=IF(OR(AND([% Complete]@row = 1, [Expected % Complete]@row = 1), AND([% Complete]@row = 1, [Expected % Complete]@row < 1)), "Complete", IF(AND([% Complete]@row >= [Expected % Complete]@row, [% Complete]@row > 0), "On Track", IF(AND([% Complete]@row < [Expected % Complete]@row, [% Complete]@row > 0), "Behind", IF(AND([% Complete]@row < [Expected % Complete]@row, [% Complete]@row = 0), "Not Started/Behind", "Not Started"))))
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.1K Get Help
 352 Global Discussions
 198 Industry Talk
 427 Announcements
 4.4K Ideas & Feature Requests
 135 Brandfolder
 127 Just for fun
 128 Community Job Board
 444 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!