Nested If Formula

Hello,

 

I am trying to create a nested IF formula based on the criteria below. Below is also a screenshot of what I am trying to do:

 

 

=IF([% Complete]1=0,"Not started"

=IF([% Complete]1>=0 and <=1, "On track"

<code class="CodeInline">=IF([%Complete)]1=1,"Completed"
=IF([%Complete)]1<=1 and Due Date is past, "Missed"</p><p>&nbsp;</p><p>Additionally, I would like to create a status column that shows the quarter, half, three-quarter, and full progress bars, where 1-25 has no bar, 26-50 has quarter, 51-75 has half, 76-99 has three quarter, and 100 has full. In tandem, I would like there would be a yellow status bubble for projects that are not started, green for on track, blue for complete, and red for not complete by due date (missed), all that are filled in automatically based on the nested if statements.</p><p>&nbsp;</p><p>Thank you in advance!</p><p><img src=https://us.v-cdn.net/6031209/uploads/drupal_attachment/files/24/d4/24d4fa88f84a20df43220d0f885e3163.PNG alt="Nested IF Problem.PNG">

Comments

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭
    edited 12/15/19
    When nesting If's, be careful with the order because it will be first come first served.

    Status 2 formula:
    =IF(AND([Due Date]1 < TODAY(), [% Complete]1 < 1), "Missed", IF([% Complete]1 = 0, "Not started", IF([% Complete]1 < 1, "On track", IF([% Complete]1 = 1, "Completed", "error"))))
    Progress Bar formula:
    =IF([% Complete]1 < 0.25, "Empty", IF([% Complete]1 < 0.5, "Quarter", IF([% Complete]1 < 0.75, "Half", IF([% Complete]1 < 1, "Three Quarter", "Full"))))
    


    Status Ball formula:
    =IF(AND([Due Date]1 < TODAY(), [% Complete]1 < 1), "Red", IF([% Complete]1 = 0, "Yellow", IF([% Complete]1 < 1, "Green", IF([% Complete]1 = 1, "Blue", "error"))))
    


    A mistake I've seen when trying to implement this:

    Converting the number ([% Complete]) to text (in [Status 2] column and then using that result to convert it back to a number or to use the results for another nested if.

    The Status Ball calc might naturally be pointing to Status 2 but that is not nice to the CPU and I've seen odd results due to waiting for long complex interrelated formulas to process.
  • Gwyneth C
    Gwyneth C ✭✭✭✭✭✭

    A note about the editing of this post ...

    We've moved to a different editor and I've edited this post to show an example of using the "code" format to make it a little easier to see the structure of formulas and functions. For more information, see the post by @Genevieve P.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!