# Changing Status and Health Based on Percentage

Options
edited 07/25/22

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!

• ✭✭✭✭✭✭
Options

Try these in their respective columns...

Status:

=IF([Percent Complete]@row = 1, "Complete", IF([Percent Complete]@row = 0, "Not Started", "In Progress"))

Percent Complete:

=IF([Percent Complete]@row = 1, "Green", IF([Percent Complete]@row = 0, "Red", "Yellow"))

• ✭✭✭✭✭✭
Options

Try these in their respective columns...

Status:

=IF([Percent Complete]@row = 1, "Complete", IF([Percent Complete]@row = 0, "Not Started", "In Progress"))

Percent Complete:

=IF([Percent Complete]@row = 1, "Green", IF([Percent Complete]@row = 0, "Red", "Yellow"))

• Options

Tried both of these but unfortunately they're coming up as "unparsable" :(

• ✭✭✭✭✭✭
Options

My apologies. I had some typos. Change every instance of [Percent Complete] to [Percent Completed] so that the columns referenced match the columns in the sheet.

• ✭✭
Options

I am trying to do the opposite but it's not working. =IF([Status]@row = "Complete", 100, IF([Status]@row = "Not Started", 0, 50)).

• ✭✭✭✭✭✭
Options

@J Bauer Can you provide more context as to why it is "not working"? Are you getting an error message or an unexpected output?

• ✭✭
Options

@Paul Newcome - It is just displaying the formula. When doing it the other way it works. Maybe a dependency on the sheet???

• ✭✭✭✭✭✭
Options

@J Bauer You cannot use formulas in columns being populated via the dependency settings.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!