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" 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. Thank you in advance!<img src=https://us.v-cdn.net/6031209/uploads/drupal_attachment/files/24/d4/24d4fa88f84a20df43220d0f885e3163.PNG alt="Nested IF Problem.PNG">

Find more posts tagged with

Sheets

Formulas

functionality

Project Management

Comments

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

Sign in to join the conversation!

Quick Links

Trending discussions

Getting error message in approval email
Hey, Is anyone else facing this issue in the approval notifications sent through smartsheet
Add Package Count Based on Customer
I have a sheet with a list of customers in one column, and then the following columns are City, Monday, Tuesday, Wednesday, Thursday, Friday. I need help with a formula that I can put in my sheet summary so that if the customer column says Staples (It can say this in multiple rows) that it will tell me the total package…
Automation not triggering all of the time
Hi Smartsheet community, I've always tried to answer questions but this is my first time posting. So I have formula =IF([Project Status]@row = "Complete", 1, 0) set up in the "Complete Check - Hide column" to check the box when the project status is marked complete. The project status column is tied to several other sheets…