If Condition

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!

Find more posts tagged with

IF(

Accepted answers

VBAGuru

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

VBAGuru

Your welcome!

All comments

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?

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.