# Calculating Schedule Health - RYBG

Options
✭✭✭✭

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

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

Options

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

Options

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

• ✭✭✭✭
Options

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

edited 09/01/21
Options

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

• ✭✭✭✭
Options

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.

Options

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?

• ✭✭✭✭
Options

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?

Options

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

• ✭✭✭✭
Options

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

Options

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

• ✭✭✭✭
edited 09/02/21
Options

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

Options

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

• ✭✭✭✭
Options

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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!