Adding to RAG Formula
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
-
@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
Answers
-
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")))))
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
@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.
-
@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:
-
@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
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
@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)
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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?
-
@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")))))
-
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"?
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Red, Yellow, Green, Gray
-
@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")))))
-
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"
-
@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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 495 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
