If Status = specific status then % complete will be changed

Hi,

I have taken out all dependancies and try to have the following:

If a field in [ status] column indicates "Complete" , then I want to have 100% in the field from the column [% Complete] AND

If a field in [ status] column indicates "Ready for testing" , then I want to have 90%in the field from the column [% Complete] AND

If a field in [ status] column indicates "in Progress" , then I want to have 25%in the field from the column [% Complete] AND

If a field in [ status] column indicates "Not Started" or the field is blank , then I want to have 0%in the field from the column [% Complete]

Answers

  • Anelise Wilhelm
    Anelise Wilhelm ✭✭✭✭

    Hi @Geraldine Try this formula in your [% Complete] column. It should do what you want. Make sure that the column is formatted at %.

    =IF(Status@row = "Complete", 1, IF(Status@row = "Ready for Testing", 0.9, IF(Status@row = "In Progress", 0.25, IF(Status@row = "Not Started", 0))))

    Hope it works for you 😃

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭

    @Geraldine

    You can accomplish this with a series of nested IFs. First, decide if your % Complete column is going to be text/number or text/number with the % formatting applied, because that will determine what values you set in your IFs.

    With percentage format applied, you'll want to set your values as parts of 1; so 1 for 100%, .9 for 90%, and so on. If you plan on using this field for calculations, go with this option.

    Without percent formatting, enter the 5 values as text; 100%, 90%, etc.

    Formula in the [% Complete] column for % formatted column:

    =IF(Status@row = "Complete", 1, IF(Status@row = "Ready for Testing", .9, IF(Status@row = "In Progress", .25, IF(Status@row = "Not Started", 0, ""))))

    For regular text/number:

    =IF(Status@row = "Complete", "100%", IF(Status@row = "Ready for Testing", "90%", IF(Status@row = "In Progress", "25%", IF(Status@row = "Not Started", "0%", ""))))

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

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