Formula not responding


I'm hitting a bit of a road block. I think I am overthinking this more than what it should be but figured I would get a different set of eyes on my issue.

This is my formula

=IF([% Complete]@row = 0, "5-Not Started", IF(Status28 = "6-Complete", "2-In Queue", IF([% Complete]@row = 1, "6-Complete", "1-In Progress")))

This is a screenshot of the row.

Based on everything how it is set the cell should be "1-In Progress" Which it is not triggering correctly. Does anyone have any ideas on what I am missing here?


  • Michelle Basson
    Michelle Basson Overachievers Alumni
    edited 06/23/24

    Good day @John P.

    The IF statements in your formula seems to be contradicting to one another.
    If you could add another screenshot where the headings of your columns are visible, that would be great.

    Could you clarify the following:
    IF(Status28 = "6-Complete", "2-In Queue", - with this section you are saying that if the status is Complete then make the status In Queue, is this what you want to happen? and is this formula in the Status column?

    Is your intention to change the status based on the percentage completed?
    for example:
    if 0% then in Queue
    if larger than 0% then In Progress
    if 100% then Complete

    if so, try this formula:

    =IF([% Complete]@row = 0, "2-In Queue", IF([% Complete]@row = 1, "6-Complete", "1-In Progress"))

    I see you have a 5-Not Started (which I have not included in the formula above). I am not sure what the criteria is for this? If you could give me the criteria for not started and in queue that would be great.

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

    Michelle Basson

    Smartsheet Overachiever Alumni | Solution Consultant | Lover of everything Smartsheet


  • John P.
    John P. ✭✭

    Hello Michelle,

    Thank you for your response. My initial code was just as you listed with % complete, but the 0% was the not started. The "5-Not started" status is the default state.

    Order of sequence.

    If row 28 changes to "6-Complete" than row 30 would change to "2-In Queue".

    if larger than 0% then "1-In Progress"
    if 100% then "6-Complete"

    If none of these are true than "5-not started"

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!