Adding to RAG Formula

Options

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

Best Answer

  • Kaleb
    Kaleb ✭✭✭✭
    Answer ✓
    Options

    @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

«1

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    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.
    Genevieve P. Employee Admin
    Options

    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
    Paul Newcome ✭✭✭✭✭✭
    Options

    @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
    Kaleb ✭✭✭✭
    Options

    @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
    Kaleb ✭✭✭✭
    Options

    @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.
    Genevieve P. Employee Admin
    Options

    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
    Kaleb ✭✭✭✭
    Options

    @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.
    Genevieve P. Employee Admin
    Options

    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
    Kaleb ✭✭✭✭
    edited 04/20/22
    Options

    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
    Kaleb ✭✭✭✭
    Options

    @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.
    Genevieve P. Employee Admin
    Options

    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
    Kaleb ✭✭✭✭
    Options

    Red, Yellow, Green, Gray

  • Kaleb
    Kaleb ✭✭✭✭
    Options

    @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
    Kaleb ✭✭✭✭
    Options

    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
    Kaleb ✭✭✭✭
    Options

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!