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> </p><p>Additionally, I would like to create a status column that shows the quarter, half, threequarter, and full progress bars, where 125 has no bar, 2650 has quarter, 5175 has half, 7699 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> </p><p>Thank you in advance!</p><p><img src=https://us.vcdn.net/6031209/uploads/drupal_attachment/files/24/d4/24d4fa88f84a20df43220d0f885e3163.PNG alt="Nested IF Problem.PNG">
Comments

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. 
Thank you very much, Craig! This definitely helps!
Regards,
Brandon

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
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.4K Get Help
 367 Global Discussions
 202 Industry Talk
 432 Announcements
 4.4K Ideas & Feature Requests
 137 Brandfolder
 129 Just for fun
 128 Community Job Board
 446 Show & Tell
 28 Member Spotlight
 1 SmartStories
 284 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!