Nested If Formula

Brandon Schwartz

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">

J. Craig Williams

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.

Brandon Schwartz

Thank you very much, Craig! This definitely helps!

 

Regards,

Brandon

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.

https://community.smartsheet.com/discussion/64751/faq-how-do-i-use-rich-text-when-i-m-posting-a-question-discussion-or-comment