Calculating Schedule Health - RYBG

OS23

Hello,

I am a beginner Smartsheet user and I have listed my set of requirement to track my schedule health task. The requirements are based off % Complete and % Time Elapsed.

Requirements below:

1. Tasks that show status of “Complete” - Blue
2. Status in progress, where the % Complete is greater than or equal to Time elapsed. - Green
3. Status in Progress, % complete is less than the time elapsed % AND time elapsed % is no more than 20 % greater than the completed %. - Yellow
4. Status in Progress & Not Started, % complete is less than the time elapsed % AND time elapsed % is more than 20 % greater than the completed % - RED
5. Task that show any one of the fields blank (Optional).

My attempt at creating this formula -

=IF(OR([Start Date]@row = "", [End Date]@row = "", [% Complete]@row = "", [Status]@row = ""), "", 

IF([Status]@row = "Complete", "Blue",

IF([Status]@row = "In Progress", [% Complete]@row >= [% Time Elapsed]@row, "Green",

IF([Status]@row = "In Progress", [% Complete]@row < [% Time Elapsed]@row, "Yellow",  

IF([Status]@row = "In Progress", [% Time Elapsed]@row, [% Complete] > 0.2, "Yellow", 

IF([Status]@row = "Not Started", [% Time Elapsed]@row, [% Complete] < 0.2, "Red",

IF([Status]@row = "In Progress", [% Time Elapsed]@row, [% Complete] < 0.2, "Red"))))) 

Capture.JPG

I am getting an unsparesable error. Any help and guidance is greatly appreciated.

Please and Thank you!

Genevieve P.

Hi @OS23

Good catch! Yes, we can wrap an IFERROR statement around each division. I have the output be 0 instead of "" (blank).

=IF(OR([Start Date]@row = "", [End Date]@row = "", [% Complete]@row = "", Status@row = ""), "", IF(Status@row = "Complete", "Blue", IF(AND([% Complete]@row >= [% Time Elapsed]@row, Status@row = "In Progress"), "Green", IF(AND(IFERROR([% Time Elapsed]@row / [% Complete]@row, 0) <= 1.2, Status@row = "In Progress"), "Yellow", IF(AND(OR(Status@row = "Not Started", Status@row = "In Progress"), IFERROR([% Time Elapsed]@row / [% Complete]@row, 0) > 1.2), "Red")))))

Genevieve P.

Hi @OS23

It looks like you're just missing the row reference after your [% Complete]  in the last three statements:

Here...

IF([Status]@row = "In Progress", [% Time Elapsed]@row, [% Complete]@row > 0.2, "Yellow", 

IF([Status]@row = "Not Started", [% Time Elapsed]@row, [% Complete]@row < 0.2, "Red",

IF([Status]@row = "In Progress", [% Time Elapsed]@row, [% Complete]@row < 0.2, "Red"))))) 

=IF(OR([Start Date]@row = "", [End Date]@row = "", [% Complete]@row = "", [Status]@row = ""), "",  IF([Status]@row = "Complete", "Blue", IF([Status]@row = "In Progress", [% Complete]@row >= [% Time Elapsed]@row, "Green", IF([Status]@row = "In Progress", [% Complete]@row < [% Time Elapsed]@row, "Yellow",  IF([Status]@row = "In Progress", [% Time Elapsed]@row, [% Complete]@row > 0.2, "Yellow",  IF([Status]@row = "Not Started", [% Time Elapsed]@row, [% Complete]@row < 0.2, "Red", IF([Status]@row = "In Progress", [% Time Elapsed]@row, [% Complete]@row < 0.2, "Red"))))) 

If a column is being recognized in a formula it will light up in a colour. In your screen capture you can see that these three column references are grey because they're missing the @row row detail.

I haven't actually gone through the logic of the formula, so let me know if you're not seeing the outcome you'd like (once we get rid of the error) and I'm happy to jump in again!

Cheers,

Genevieve

OS23

Hi Genevieve,

Thanks for pointing that out, however, the Formula is presenting me with incorrect argument. I have individually tried Green and 2 Yellows and they work, but when I combine them in one formula they do not.

=IF(OR([Start Date]@row = "", [End Date]@row = "", [% Complete]@row = "", Status@row = ""), "",

IF(Status@row = "Complete", "Blue",

IF([% Complete]@row <= [% Time Elapsed]@row, Status@row = "In Progress", "Green",

IF([% Complete]@row < [% Time Elapsed]@row, Status@row = "In Progress", "Yellow"

IF([% Time Elapsed]@row < [% Complete]@row = "0.2", Status@row = "In Progress", "Yellow"

IF(Status@row = "Not Started", [% Time Elapsed]@row, [% Complete]@row < 0.2, "Red",

IF(Status@row = "In Progress", [% Time Elapsed]@row, [% Complete]@row < 0.2, "Red")))))))

If you need more information, please let me know. Thank you for your assistance.

Genevieve P.

Hi @OS23

I've broken down your formula below. Whenever you have more than one statement inside of an IF, we need to state if these are an OR or if it's an AND. For most of your statements it's an AND function that's missing.

Also, to say that % Complete is no more than 20% over, we can simply add 0.2 to % Complete, like so:

[% Complete]@row + 0.2

Then compare this value to the [% Time Elapsed]

Try this:

=IF(OR([Start Date]@row = "", [End Date]@row = "", [% Complete]@row = "", Status@row = ""), "",

IF(Status@row = "Complete", "Blue",

IF(AND([% Complete]@row <= [% Time Elapsed]@row, Status@row = "In Progress"), "Green",

IF(AND([% Time Elapsed]@row < [% Complete]@row + 0.2, Status@row = "In Progress"), "Yellow",

IF(AND(OR(Status@row = "Not Started", Status@row = "In Progress"), [% Time Elapsed]@row > [% Complete]@row + 0.2), "Red")))))

Keep in mind we're adding 20% on top of your % Complete, so this would change it from being 5% to 25%. Is this the value you're looking to measure by, or were you meaning 20% in relation to the Time Elapsed?

Let me know if the output is correct, here!

Cheers,

Genevieve

OS23

Hi Genevieve,

The formula works (Thank you) but the 20% is in relation to % Time Elapsed & % Complete. So, the second part of yellow is Time Elapsed is no more than 20 % of the completed %. And for red, it should Time Elapsed % is more than 20 % of the total % complete.

Appreciate the help.

Genevieve P.

Hi @OS23

Try this:

=IF(OR([Start Date]@row = "", [End Date]@row = "", [% Complete]@row = "", Status@row = ""), "", IF(Status@row = "Complete", "Blue", IF(AND([% Complete]@row >= [% Time Elapsed]@row, Status@row = "In Progress"), "Green", IF(AND([% Time Elapsed]@row / [% Complete]@row <= 1.2, Status@row = "In Progress"), "Yellow", IF(AND(OR(Status@row = "Not Started", Status@row = "In Progress"), [% Time Elapsed]@row / [% Complete]@row > 1.2), "Red")))))

For the Yellow and Red, we're using the calcualtion [% Time Elapsed]@row / [% Complete]@row to see if it's greater than or less than 1.2. Does that make sense?

OS23

Hi Genevieve,

Yes, it does and now it is showing the results that I want to see. THANK YOU SO MUCH.

Last question - I'm getting an error in schedule health column when my % Complete is 0 as #Divide by Zero. Is there anyway to make it blank when an error like this is shown?

Genevieve P.

Hi @OS23

Good catch! Yes, we can wrap an IFERROR statement around each division. I have the output be 0 instead of "" (blank).

=IF(OR([Start Date]@row = "", [End Date]@row = "", [% Complete]@row = "", Status@row = ""), "", IF(Status@row = "Complete", "Blue", IF(AND([% Complete]@row >= [% Time Elapsed]@row, Status@row = "In Progress"), "Green", IF(AND(IFERROR([% Time Elapsed]@row / [% Complete]@row, 0) <= 1.2, Status@row = "In Progress"), "Yellow", IF(AND(OR(Status@row = "Not Started", Status@row = "In Progress"), IFERROR([% Time Elapsed]@row / [% Complete]@row, 0) > 1.2), "Red")))))

OS23

That did the trick. Thank you so so so much!

Have a pleasant day ahead!

Genevieve P.

Phew, so glad we got it in the end! 🙂I hope you have a good day as well.

OS23

Hi Genevieve,

I need to have 2 minor additions to the formula, and their requirements below:

1. If my end date is Past due - my health should remain red until compete (blue). Currently, my past dueddates ues dates task at 90 % gives me a yellow.
2. If I have a task not started and at 0% complete - i'd like to show red instead of blank.

=IF(OR([Start Date]@row = "", [End Date]@row = "", [% Complete]@row = "", Status@row = ""), "", IF(Status@row = "Complete", "Blue", IF(AND([% Complete]@row >= [% Time Elapsed]@row, Status@row = "In Progress"), "Green", IF(AND(IFERROR([% Time Elapsed]@row / [% Complete]@row, 0) <= 1.25, Status@row = "In Progress"), "Yellow", IF(AND(OR(Status@row = "Not Started", Status@row = "In Progress"), IFERROR([% Time Elapsed]@row / [% Complete]@row, 0) > 1.25), "Red")))))

Once again thanks for helping and hope to hear from you

Capture2.JPG

Genevieve P.

Hi @OS23

No problem! We can add in those two criteria.

1) We can check to see if the Date in the End Date column is in the past compared to TODAY and then return Red if it is, and it's not complete yet. Keep in mind that the TODAY function will only be able to recognize today's date if the sheet is opened/refreshed in some way. See this article for more information.

IF(AND(OR(Status@row = "Not Started", Status@row = "In Progress"), OR(IFERROR([% Time Elapsed]@row / [% Complete]@row, 0) > 1.25), [End Date]@row < TODAY()), "Red"

I'll also place this rule BEFORE the Yellow rule, so the formula looks for this date first:

=IF(OR([Start Date]@row = "", [End Date]@row = "", [% Complete]@row = "", Status@row = ""), "", IF(Status@row = "Complete", "Blue", IF(AND([% Complete]@row >= [% Time Elapsed]@row, Status@row = "In Progress"), "Green", IF(AND(OR(Status@row = "Not Started", Status@row = "In Progress"), OR(IFERROR([% Time Elapsed]@row / [% Complete]@row, 0) > 1.25, [End Date]@row < TODAY())), "Red", IF(AND(IFERROR([% Time Elapsed]@row / [% Complete]@row, 0) <= 1.25, Status@row = "In Progress"), "Yellow")))))

2) The reason it's blank is because of your OR statement at the beginning. We can start the entire formula with a Red statement to look for "Not Started" and 0%:

=IF(AND(Status@row = "Not Started", [% Complete]@row = 0), "Red",

Then we can move on to look for blanks:

=IF(AND(Status@row = "Not Started", [% Complete]@row = 0), "Red", IF(OR([Start Date]@row = "", [End Date]@row = "", [% Complete]@row = "", Status@row = ""), "", IF(Status@row = "Complete", "Blue", IF(AND([% Complete]@row >= [% Time Elapsed]@row, Status@row = "In Progress"), "Green", IF(AND(OR(Status@row = "Not Started", Status@row = "In Progress"), OR(IFERROR([% Time Elapsed]@row / [% Complete]@row, 0) > 1.25, [End Date]@row < TODAY())), "Red", IF(AND(IFERROR([% Time Elapsed]@row / [% Complete]@row, 0) <= 1.25, Status@row = "In Progress"), "Yellow"))))))

Let me know if this is the structure you're looking for!

Cheers,

Genevieve

OS23

Thank you so much. You are my savior once again.