If Condition

Paula D

I have this condition which works well (copied from line 89 of my plan)

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

I'd like to add two conditions:

1st: if the %complete is 90%, then the cell should change to "Scheduled"

What I thought would work:

=IF([% Complete]85 = 1, "Complete", IF([% Complete]85 = 0, "Not Started", IF([% Complete]85 = 90, "Scheduled", "In Progress"))) isn't working.

2nd: If a - (negative sign) is entered in the %complete field, the status field changes to "Backlog"

Can someone help me with this formula? Much appreciated!

VBAGuru

put in a % inside the quotes for the negative: "-%"

VBAGuru

Your welcome!

VBAGuru

Presuming that your percent complete column is formatted as %:

=IF([% Complete]@row = 1, "Complete", IF([% Complete]@row = 0, "Not Started", IF([% Complete]@row = 0.9, "Scheduled", IF([% Complete]@row = "-", "Backlog", "In Progress"))))

Paula D

Hi, the 90% condition is working! However, the negative switches the status to In Progress. I don't have to use a negative sign if you can think of another way to make the switch.

Thank you for the quick help

VBAGuru

put in a % inside the quotes for the negative: "-%"

Paula D

Thank you so much!!!!

VBAGuru

Your welcome!

Irtaza

How do I select range when writing an if statement; for instance, I want to say I would like to know how to write a formula for one column with different information. I was able to do it in excel got the result as you can see below but Smartsheet I am having a hard time understanding it. Can you please help how it can be implemented?

=IF(G2>99%,"Complete", IF(G2<1%,"Not Started", IF(G2>1%,"In-Progress")))

Note: Statement above I used in excel.

Paul Newcome

@Irtaza Try this:

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

Irtaza

Thanks Paul, I used what you told me and still I am getting this error.

Irtaza

@VBAGuru any help would be appreciated please.

VBAGuru

@Irtaza The formula looks correct, but if that is a screen shot, it would seem that that [% Complete] part is not "linked" correctly. A Smartsheet issue if copying/pasting a formula from text. They should be a color. I would suggest trying to relink manually.

Paul Newcome

@Irtaza Make sure the [Column Name] in the formula matches the column name you are using in your sheet.

Irtaza

Thanks Guy it worked :)

Paul Newcome

Happy to help. 👍️

VBAGuru

Glad that worked for you!

Irtaza

@Paul Newcome or @VBAGuru I would like add another status "hold" into the IF statement. what should be my equation be?