Risk Formula

I need help to come up with the risk formula. I tried many different formulas and tried to modify it but just can't seem to have desired results. So these are the conditions I MUST meet:
Program is a parent of all projects listed under and if any of the projects turns red, yellow or green then entire program turns red, or yellow if not red or green if not yellow or red.
Mark it Blue:
- if project is on hold
Mark it Green:
- if progress is made before the start date, meaning % complete > 0%
- OR if today's date has not exceeded the start date
- OR if the status is "in progress" and %complete is more then expected % complete
- OR if the status is "In Progress" but not more than 5% behind the expected % complete
- OR if the status % complete is 100% AND the status is "Complete"
Mark it Yellow:
- if the status is "Not started" and today's date passed start date but expected % complete is less or equal to 10%
- OR if the status is "in progress" but % complete is behind by 20% or less compare to expected % complete
Mark it Red:
- if the status is "Not Started" and today's date passed the start date where expected % complete exceeds 10% or more
- OR if the status is "In Progress" and the project falls behind more then 20% (% complete < expected % complete by 20% or more)
Have attached the test program and Risk formulas i tried does not provide results I seek. Please Help!!
Best Answers
-
In general, you want your colors to choose the highest applicable out of the following (in this order):
Blue
Red
Yellow
Green
So writing the formula in that order will be a bit easier. Try this, but remove the line breaks I added for clarity:
=if(Status@row = "On Hold", "Blue",
if(or(and(status@row="Not Started",today()>Start@row,[Expected % Complete]@row>0.1), and(status@row="In Progress",[% Complete]@row<[Expeted % Complete]@row -0.2)),"Red",
if(or(and(status@row="Not Started",today()>Start@row), (status@row="In Progress"),"Yellow",
"Green")))
This way you are checking for your most critical conditions first and basically saying and it saves you from having to trouble shoot all the green conditionals.
if it is on hold, ignoring all other conditions, mark it blue
if it is very late, mark it red
if it is late but not very late, mark it yellow
otherwise mark it green.
******
I'm not sure what was precisely going wrong in your version of the formula, but I'd suspect it was that you got an and() where you needed an or() or something similar when writing out the green criteria - reversing the order lets you skip that.
-
@Jgorsich, Thank you for your help.
I did slight modifications to your code to get desirable results.
For Program level row to capture progression of all child projects I used a column formula:
=IF(COUNTIF(CHILDREN(), "Red") > 0, "Red", IF(COUNTIF(CHILDREN(), "Yellow") > 0, "Yellow", IF(COUNTIF(CHILDREN(), "Blue") > 0, "Blue", "Green")))
And for each child project columns I used the following formula:
=IF(Status@row = "On Hold", "Blue",
IF(OR(
AND(Status@row = "Not Started", TODAY() > Start@row, [Expected % Complete]@row > 0.1),
AND(Status@row = "In Progress", [% Complete]@row < [Expected % Complete]@row - 0.2)
), "Red",
IF(OR(
AND(Status@row = "Not Started", TODAY() > Start@row),
AND(Status@row = "In Progress", [% Complete]@row < [Expected % Complete]@row - 0.05, [% Complete]@row >= [Expected % Complete]@row - 0.2)
), "Yellow",
"Green")))I tested out each condition and it works how I intended it to work.
Thank you for your help and support, @Jgorsich.
Answers
-
I am trying this formula… what am I missing:
" =IF(Status@row = "On Hold", "Blue", IF(OR([% Complete]@row > 0, TODAY() < Start@row, AND(Status@row = "In Progress", [% Complete]@row >= [Expected % Complete]@row), AND(Status@row = "In Progress", [% Complete]@row >= [Expected % Complete]@row - 0.05), AND(Status@row = "Complete", [% Complete]@row = 1)), "Green", IF(OR(AND(Status@row = "Not Started", TODAY() > Start@row, [Expected % Complete]@row <= 0.1), AND(Status@row = "In Progress", [% Complete]@row >= [Expected % Complete]@row - 0.2, [% Complete]@row < [Expected % Complete]@row)), "Yellow", IF(OR(AND(Status@row = "Not Started", TODAY() > Start@row, [Expected % Complete]@row > 0.1), AND(Status@row = "In Progress", [% Complete]@row < [Expected % Complete]@row - 0.2)), "Red", "Red"))))"
-
In general, you want your colors to choose the highest applicable out of the following (in this order):
Blue
Red
Yellow
Green
So writing the formula in that order will be a bit easier. Try this, but remove the line breaks I added for clarity:
=if(Status@row = "On Hold", "Blue",
if(or(and(status@row="Not Started",today()>Start@row,[Expected % Complete]@row>0.1), and(status@row="In Progress",[% Complete]@row<[Expeted % Complete]@row -0.2)),"Red",
if(or(and(status@row="Not Started",today()>Start@row), (status@row="In Progress"),"Yellow",
"Green")))
This way you are checking for your most critical conditions first and basically saying and it saves you from having to trouble shoot all the green conditionals.
if it is on hold, ignoring all other conditions, mark it blue
if it is very late, mark it red
if it is late but not very late, mark it yellow
otherwise mark it green.
******
I'm not sure what was precisely going wrong in your version of the formula, but I'd suspect it was that you got an and() where you needed an or() or something similar when writing out the green criteria - reversing the order lets you skip that.
-
@Jgorsich, Thank you for your help.
I did slight modifications to your code to get desirable results.
For Program level row to capture progression of all child projects I used a column formula:
=IF(COUNTIF(CHILDREN(), "Red") > 0, "Red", IF(COUNTIF(CHILDREN(), "Yellow") > 0, "Yellow", IF(COUNTIF(CHILDREN(), "Blue") > 0, "Blue", "Green")))
And for each child project columns I used the following formula:
=IF(Status@row = "On Hold", "Blue",
IF(OR(
AND(Status@row = "Not Started", TODAY() > Start@row, [Expected % Complete]@row > 0.1),
AND(Status@row = "In Progress", [% Complete]@row < [Expected % Complete]@row - 0.2)
), "Red",
IF(OR(
AND(Status@row = "Not Started", TODAY() > Start@row),
AND(Status@row = "In Progress", [% Complete]@row < [Expected % Complete]@row - 0.05, [% Complete]@row >= [Expected % Complete]@row - 0.2)
), "Yellow",
"Green")))I tested out each condition and it works how I intended it to work.
Thank you for your help and support, @Jgorsich.
-
Excellent, glad I could help!
Note that this will mean your default condition is green - so something missing information will ALSO go green (which may not be what you want, vs forcing it into a purple category or something).
If that is problematic, you may want to create another condition based off of a countm() formula having the correct value for your necessary columns and have that trigger purple even before any of the red/yellow/green criteria are evaluated (probably not blue though - if data is missing from a project on hold, you probably don't care too much).
-
That makes me wonder,
is there a way to have more colors added in Risk section. I used the column property with column type as "Symbols" and I only see limited color options. How can we add more color options.
-
You could use conditional formatting and tie it to a number (replace your "red" with 3, for example, and then have all values of 3 conditionally formatted to be red). This is actually what I thought you were doing :).
-
Yes, I was asking in terms of introducing more color coded symbols for other status and purposes etc.
-
As of now, limitation is red, yellow, green and either blue to gray. I wish would could have an option to add and/or pick more colored symbols.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.5K Get Help
- 433 Global Discussions
- 152 Industry Talk
- 494 Announcements
- 5.3K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 77 Community Job Board
- 505 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 307 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!