Changing Status and Health Based on Percentage

MaddyDesautels
edited 07/25/22 in Formulas and Functions

Hi guys!

I'm fairly new to Smartsheets (and project management) and i'm the first and only project manager at my company. I was looking for help with conditional formatting and and IF AND function. Any and all help would be greatly appreciated!

What I'm looking to do is (seemingly) basic:

If my "Percentage Completed" row is = 100% then my status row should be "Complete" and my health row should be "Green"

If my "Percentage Completed" row is less than 100% then my status row should be "In Progress" and my health row should be "Yellow"

And lastly, If my "Percentage Completed" row is 0% then my status row should be "Not Started" and my health row should be "Red"

The formula I have for changing status (based on percentage complete) is below, however the "in progress" is not working. I still haven't found a formula or Conditional Formatting for the health row.

=IF([Percent Completed]@row=100,"Complete",IF([Percent Completed]@row=0,"Not Started",IF(AND([Percent Completed]@row > 0, [Percent Completed]@row < 100, "In Progress"))))


I've also attached a photo so that you can get a better idea of what I'm looking for!


Thank you in advance for your help!

Madeline

Best Answer

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!