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
Check out the Formula Handbook template!