Adding to RAG Formula

Kaleb

Good Day All:

I am Trying to add a Gray Harvey Ball to Task that have Not Started and have 0% Complete Plan and 0% Complete Actual.

Current Formula:

=IF([% Complete (Actual)]@row = 1, "Blue", IF(TODAY() > [Finish Date (Actual)]@row, "Red", IF(TODAY(10) >= [Finish Date (Actual)]@row, IF([% Complete (Actual)]@row < 0.75, "Yellow", "Green"))))

Modified Formula to Include Not Started and 0% Complete Plan and 0% Complete Actual:

=IF([% Complete (Actual)]@row = 1, "Blue", IF(TODAY() > [Finish Date (Actual)]@row, "Red", IF(OR(AND([Start Date (Actual)]@row < TODAY(), [% Complete (Plan)]@row = 0, "Gray", IF(TODAY(10) >= [Finish Date (Actual)]@row, IF([% Complete (Actual)]@row < 0.75, "Yellow", "Green")))))))

Thanks for the help in Advance All

Kaleb

@Genevieve P. I have Figured it Out. Thank GOD!

Final Formula for Project Health:

=IF([Schedule Status]@row = "Not Started", "Gray", IF(AND([Schedule Status]@row = "Behind", [% Complete (Plan)]@row - [% Complete (Actual)]@row > 0.1), "Red", IF(AND([Schedule Status]@row = "Behind", [% Complete (Plan)]@row - [% Complete (Actual)]@row < 0.9), "Yellow", IF(OR([Schedule Status]@row = "On Track", [% Complete (Actual)]@row = [% Complete (Plan)]@row), "Green"))))

Thanks for All your Help @Genevieve P. and @Paul Newcome

Paul Newcome

Try this:

=IF([% Complete (Actual)]@row = 1, "Blue", IF(TODAY() > [Finish Date (Actual)]@row, "Red", IF(OR([Start Date (Actual)]@row < TODAY(), [% Complete (Plan)]@row = 0), "Gray", IF(TODAY(10) >= [Finish Date (Actual)]@row, IF([% Complete (Actual)]@row < 0.75, "Yellow", "Green")))))

Genevieve P.

Hi @Kaleb

It looks like you just need to close off the AND Function before you tell it to say "Gray". You also don't need the OR.

IF(AND(), IF....

Try this:

=IF([% Complete (Actual)]@row = 1, "Blue", IF(TODAY() > [Finish Date (Actual)]@row, "Red", IF(AND([Start Date (Actual)]@row < TODAY(), [% Complete (Plan)]@row = 0), "Gray", IF(TODAY(10) >= [Finish Date (Actual)]@row, IF([% Complete (Actual)]@row < 0.75, "Yellow", "Green")))))

Are you wanting to use OR, though? Or AND?

Here's the OR structure, it's the same as the AND where you just need to close it off before telling it what colour:

=IF([% Complete (Actual)]@row = 1, "Blue", IF(TODAY() > [Finish Date (Actual)]@row, "Red", IF(OR([Start Date (Actual)]@row < TODAY(), [% Complete (Plan)]@row = 0), "Gray", IF(TODAY(10) >= [Finish Date (Actual)]@row, IF([% Complete (Actual)]@row < 0.75, "Yellow", "Green")))))

Or were you looking for two possibilities:

• Not Started and 0 in % Complete Plan
• Not Started and 0 in % Complete Actual

In this case we would have an OR and two AND statements

IF(OR(AND(), AND())...

=IF([% Complete (Actual)]@row = 1, "Blue", IF(TODAY() > [Finish Date (Actual)]@row, "Red", IF(OR(AND([Start Date (Actual)]@row < TODAY(), [% Complete (Plan)]@row = 0), AND([Start Date (Actual)]@row < TODAY(), [% Complete (Actual)]@row = 0)), "Gray", IF(TODAY(10) >= [Finish Date (Actual)]@row, IF([% Complete (Actual)]@row < 0.75, "Yellow", "Green")))))

Let me know if any of these have worked for you!

Cheers,

Genevieve

Paul Newcome

@Genevieve P. Thanks to your comment, I see where I provided an incorrect response because I misread the original post. So thanks!

This would be my amended solution:

=IF([% Complete (Actual)]@row = 1, "Blue", IF(TODAY() > [Finish Date (Actual)]@row, "Red", IF(AND([Start Date (Actual)]@row < TODAY(), [% Complete (Plan)]@row = 0, [% Complete (Actual)]@row = 0), "Gray", IF(TODAY(10) >= [Finish Date (Actual)]@row, IF([% Complete (Actual)]@row < 0.75, "Yellow", "Green")))))

AND function for all three.

Kaleb

@Genevieve P. I used the Formula Below you Provided, but my Gray Harvey Ball does not Display when Not Started and 0% Complete for Plan and Actual

=IF([% Complete (Actual)]@row = 1, "Blue", IF(TODAY() > [Finish Date (Actual)]@row, "Red", IF(OR(AND([Start Date (Actual)]@row < TODAY(), [% Complete (Plan)]@row = 0), AND([Start Date (Actual)]@row < TODAY(), [% Complete (Actual)]@row = 0)), "Gray", IF(TODAY(10) >= [Finish Date (Actual)]@row, IF([% Complete (Actual)]@row < 0.75, "Yellow", "Green")))))

Screenshot:

Kaleb

@Paul Newcome I tried your formula as well, but same result. Let me Revisit my Criteria and I will get back to you Both. I appreciate both of your help.

CC: @Genevieve P.

Thanks

Genevieve P.

https://community.smartsheet.com/discussion/comment/326734#Comment_326734

Hi @Kaleb

This is because your Start Date (Actual) is greater than today, not less than. In your formula you have

[Start Date (Actual)]@row < TODAY()

Do you want to look instead for if the date is in the future? If so, we just need to swap < to >

=IF([% Complete (Actual)]@row = 1, "Blue", IF(TODAY() > [Finish Date (Actual)]@row, "Red", IF(OR(AND([Start Date (Actual)]@row > TODAY(), [% Complete (Plan)]@row = 0), AND([Start Date (Actual)]@row > TODAY(), [% Complete (Actual)]@row = 0)), "Gray", IF(TODAY(10) >= [Finish Date (Actual)]@row, IF([% Complete (Actual)]@row < 0.75, "Yellow", "Green")))))

Cheers,

Genevieve

Kaleb

@Paul Newcome and @Genevieve P. Here is What I am Trying to Accomplish. I hope this Make Sense.

Criteria for Health

Red: If 1 Day Past the Finish Date (Actual) and Less than 100% or If Finish Date is with 3 Days from Today and % Complete (Actual) is Less than 75%

Yellow: If the Finish Date (Actual) is within 5 Days from Today and % Complete (Actual) and %complete (Plan) has a difference of 10%  (i.e Actual is 40% and Plan is 51%). If Greater than 10% Difference, Red

Green: If % Complete (actual) and % Complete (Plan) are Equal or If % Complete (Actual) is Greater Than % Complete Plan

Not Started: If Start Date (Actual) is Greater Than Today and %Complete (Actual) and % Complete (Plan) is Zero (0)

Genevieve P.

Hi @Kaleb

We can break down each of your instructions above into their formula counterpart. The order that you list them is important, but first why don't we just translate your text into formula.

BLUE

If % Complete Actual = 100%

=IF([% Complete (Actual)]@row = 1, "Blue",

RED

If 1 Day Past the Finish Date (Actual) and Less than 100% or If Finish Date is with 3 Days from Today and % Complete (Actual) is Less than 75%

IF(OR([Finish Date (Actual)]@row < TODAY(), AND([Finish Date (Actual)]@row >=TODAY(), [Finish Date (Actual)]@row <= TODAY(3), [% Complete (Actual)]@row < 0.75)), "Red",

YELLOW

If the Finish Date (Actual) is within 5 Days from Today and % Complete (Actual) and %complete (Plan) has a difference of 10% (i.e Actual is 40% and Plan is 51%). If Greater than 10% Difference, Red

IF(AND([% Complete (Plan)]@row - [% Complete (Actual)]@row > 0.1, [Finish Date (Actual)]@row >=TODAY(), [Finish Date (Actual)]@row <= TODAY(5)), "Yellow",

GREEN

If % Complete (actual) and % Complete (Plan) are Equal or If % Complete (Actual) is Greater Than % Complete Plan

IF(OR([% Complete (Plan)]@row = [% Complete (Actual)]@row, [% Complete (Actual)]@row > [% Complete (Plan)]@row), "Green",

NOT STARTED

If Start Date (Actual) is Greater Than Today and %Complete (Actual) and % Complete (Plan) is Zero (0)

IF(AND([Start Date (Actual)]@row > TODAY(), [% Complete (Plan)]@row = 0, [% Complete (Actual)]@row = 0), "Not Started"

Put it all together:

=IF([% Complete (Actual)]@row = 1, "Blue", IF(OR([Finish Date (Actual)]@row < TODAY(), AND([Finish Date (Actual)]@row >=TODAY(), [Finish Date (Actual)]@row <= TODAY(3), [% Complete (Actual)]@row < 0.75)), "Red", IF(AND([% Complete (Plan)]@row - [% Complete (Actual)]@row > 0.1, [Finish Date (Actual)]@row >=TODAY(), [Finish Date (Actual)]@row <= TODAY(5)), "Yellow", IF(OR([% Complete (Plan)]@row = [% Complete (Actual)]@row, [% Complete (Actual)]@row > [% Complete (Plan)]@row), "Green", IF(AND([Start Date (Actual)]@row > TODAY(), [% Complete (Plan)]@row = 0, [% Complete (Actual)]@row = 0), "Not Started")))))

Let me know if this works and provides the expected output!

Cheers,

Genevieve

Kaleb

Not Started: I made an Error in my Criteria but Corrected. It Should Be the Color Gray. I Changed that, but Still Receive the Color Green when the the Start Date is in the Future and % Complete Actual and % Complete Plan is 0

Yellow (Criteria Error Too): If the Finish Date (Actual) is within 5 Days from Today and % Complete (Actual) and %complete (Plan) has a difference of 10% (i.e Actual is 40% and Plan is 51%) or % Complete (Actual) is Less Than % Complete (Plan), Yellow, but i If Greater than 10% Difference, Red

See Develop Charter Screen Shot: The Health Should be Red because it is Only 4% Complete (Actual) and the Plan calls for 20% Complete . Therefore it does not meet the criteria for Yellow which has 5 days to complete and only a 10% Difference Between Plan and Actual %.

Green: I Receive Red When % Complete (Actual) is Greater than % Complete Plan. It should be Green.

I could Share my sheet with you if that would make it easier?

Kaleb

@Genevieve P. I am almost there. I Separated Each RYG Formula and then Combined (Thanks for the Tip). However, I am having an issue with the Green Formula.

It Should be Green When:

If %Complete (Actual) is Equal to % Complete (Plan), but Only if they are not Equal to 0 OR if % Complete (Actual) is Greater Than % Complete (Plan) or If % Complete (Actual) is 100%

Green Formula:

IF(AND(OR([% Complete (Actual)]@row = [% Complete (Plan)]@row, [% Complete (Actual)]@row <> 0, [% Complete (Plan)]@row <> 0, [% Complete (Actual)]@row > [% Complete (Plan)]@row), "Green")))))

All Together Formula:

=IF(AND([% Complete (Plan)]@row - [% Complete (Actual)]@row = 0.1, [Finish Date (Actual)]@row >= TODAY(), [Finish Date (Actual)]@row <= TODAY(5)), "Yellow", IF(AND([% Complete (Plan)]@row - [% Complete (Actual)]@row > 0.1, [Finish Date (Actual)]@row >= TODAY(), [Finish Date (Actual)]@row <= TODAY(5)), "Red", IF(AND([Start Date (Actual)]@row > TODAY(), [% Complete (Plan)]@row = 0, [% Complete (Actual)]@row = 0), "Gray", IF(AND(OR([% Complete (Actual)]@row = [% Complete (Plan)]@row, [% Complete (Actual)]@row <> 0, [% Complete (Plan)]@row <> 0, [% Complete (Actual)]@row > [% Complete (Plan)]@row), "Green")))))

Genevieve P.

Hi @Kaleb

Ah! One more thing about Nested If statements. The order that you place the instructions in is important. IF Statements will stop as soon as they find a match, and won't read anything else.

This means that as soon as

[% Complete (Plan)]@row = [% Complete (Actual)]@row

Even if that's 0 for both of them, that = Green, as we put the Green statement before Gray.

Try moving it around:

=IF([% Complete (Actual)]@row = 1, "Blue", IF(OR([Finish Date (Actual)]@row < TODAY(), AND([Finish Date (Actual)]@row >=TODAY(), [Finish Date (Actual)]@row <= TODAY(3), [% Complete (Actual)]@row < 0.75)), "Red", IF(AND([% Complete (Plan)]@row - [% Complete (Actual)]@row > 0.1, [Finish Date (Actual)]@row >=TODAY(), [Finish Date (Actual)]@row <= TODAY(5)), "Yellow", IF(AND([Start Date (Actual)]@row > TODAY(), [% Complete (Plan)]@row = 0, [% Complete (Actual)]@row = 0), "Not Started", IF(OR([% Complete (Plan)]@row = [% Complete (Actual)]@row, [% Complete (Actual)]@row > [% Complete (Plan)]@row), "Green")))))

I've kept the output as "Not Started" because you also have a Blue statement, at the beginning, if the % complete is 100%.

You can either have R/Y/G/Blue or R/Y/G/Gray

Which one would you prefer?

=IF([% Complete (Actual)]@row = 1, "Blue",

should this be

=IF([% Complete (Actual)]@row = 1, "Gray"?

Kaleb

Red, Yellow, Green, Gray

Kaleb

@Genevieve P. I know I am so close on the Green Formula (In Bold). Take a Look:

=IF(AND([% Complete (Plan)]@row - [% Complete (Actual)]@row = 0.1, [Finish Date (Actual)]@row >= TODAY(), [Finish Date (Actual)]@row <= TODAY(5)), "Yellow", IF(AND([% Complete (Plan)]@row - [% Complete (Actual)]@row > 0.1, [Finish Date (Actual)]@row >= TODAY(), [Finish Date (Actual)]@row <= TODAY(5)), "Red", IF(AND([Start Date (Actual)]@row > TODAY(), [% Complete (Plan)]@row = 0, [% Complete (Actual)]@row = 0), "Gray", IF(OR([% Complete (Actual)]@row > [% Complete (Plan)]@row, [% Complete (Actual)]@row <> 0, [% Complete (Plan)]@row <> 0, "Green")))))

Kaleb

This Part of the Formula can not be 0%. 0% for Actual & Plan would be Gray not Green in this Scenario.

IF(OR([% Complete (Plan)]@row = [% Complete (Actual)]@row, [% Complete (Actual)]@row > [% Complete (Plan)]@row), "Green"

Kaleb

@Genevieve P. I used a previous Formula from another Sheet and took a Simpler Approach. However, I still can not Resolve the Green issue. I still getting Uparseable.

=IF([Schedule Status]@row = "Not Started", "Gray", IF(AND([Schedule Status]@row = "Behind", [% Complete (Plan)]@row - [% Complete (Actual)]@row > 0.1), "Red", "Yellow", IF(OR([Schedule Status]@row = "On Track", [Schedule Status]@row = "Ahead", "Green"))))