Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Mark Complete If column value is 100%

Hi Everyone,

 

I'm trying to write a formula to mark a status column one of the following based on a % complete column:

Complete If 100%
Not Started If 0%
In Progress If 0<>100%
Empty if blank

 

I'm using:

=IF([% Complete]1 = "100", "Complete", IF([% Complete]1 = "0", "Not Started", IF([% Complete]1 = "", "", "In Progress")))

 

When the % column is 0%, I get "Not Started" without issue.  However, when I mark 100% it always gives "In Progress".  I've tried changing the formula to use 100, 100%, "100%" but none of those work.

 

Anyone know how to get an IF function to do something based off a 100% value?

Comments

  • Brett Evans
    Brett Evans ✭✭✭✭✭✭

    Your 100% might = 1.00 not 100 depending on how your columns are set up.  Try changing the formula to

     

    =IF([% Complete]1 = "1", "Complete", ...

  • Michael Burstein
    edited 09/22/16

    Brilliant!  That fixed it.  Thanks for the help.

  • Old thread but this might help:

    =IF(NOT(ISBLANK([Task Name]1)), IF([% Complete]1 = 1, "Completed", IF(Finish1 < TODAY(), "*** LATE ***", IF(AND([% Complete]1 > 0, [% Complete]1 < 1), "In Process", IF([% Complete]1 = 0, "Not Started")))), "")

    First it looks to make sure there is a task name (I like to insert blank lines in my plans and this allows me to copy the formula all the way down to the bottom, without skipping blank lines.)

    Next, it marks it complete, if it sees 100% (1).

    After that, it looks to see if the end date has passed and marks it "*** LATE ***", if it has.

    Next is to flag it in process or not started.  I guess you could add other things such as late starts, starting next week, due next week, etc.

     

     

     

This discussion has been closed.