2 Conditions on 1 Status Indicator

Stevefc

I’m working on a project where I need to have to 2 conditions on 1 status indicator.

• 1 for tracking duration against today's date:

 =IF(NETWORKDAY([Start Date]9 + 1, TODAY()) / Duration9 <= [% Complete]9, "Green", "Yellow")

• 2. Tracking if a task is not completed on time:

=IF(AND(TODAY() >= [End Date]10 + 1, [% Complete]10 < 1), "Red", "Green")

Is it possible to combine the 2 statements in 1 formula? Or should I be taking another approach?

L_123

try using another if statement

 

=if(Criteria, true, false) would go to

=if(criteria, if(criteria,true,false),false)

 

This way you can have multiple conditionals stacked inside each other.

Stevefc

Thanks for the update, however, my 2 IF statements have separate criteria and outcomes, would your suggest still work?  

L_123

Yes, it will still work.

 

=if([Column1]1 <= 10, if([Column1]1 > 5, "Yellow","Red"),"Green")

 

The above will show when the value in the Column1 row 1 is:

0-5 = "Red"

6-10 = "Yellow"

11+ = "Green"

You can use this concept to even further extents, and stack as many if statements as you like. It can get rather complicated quickly though, so I would experiment with 2 if statements and see what results are given.

 

Stevefc

Thank you very much for your help so far, much appreciated.

I tried your solutions. If my first IF is true, I want a yellow status and for my second IF true a red status. IF both are false a green status. I’m just getting a red and green status no yellow, can you help?

 

=IF(NETWORKDAY([Start Date]3 + 1, TODAY()) / Duration3 >= [% Complete]3, IF(AND(TODAY() >= [End Date]3 + 1, [% Complete]3 < 1), "Yellow", "Red"), "Green")

L_123

What you have right now is 

=if(criteria, if(criteria, "yellow","red"),"green")

 

So:

Criteria 1 | Criteria 2         | Result

True        | True                | Yellow

True        | False               | Red

False       | X                     | Green

 

(the X means it isn't taken into account)

 

What you want is your second IF to be in the false category of the first.

 

=if(criteria, "yellow",if(criteria, "red","green"))

Criteria 1 | Criteria 2         | Result

True        | X                      | Yellow

True        | False               | Red

False       | True                | Green

 

 

But there are many different ways to build this out. by switching the if statements you can use my earlier/your current format.

 

Your current formula is:

=IF(NETWORKDAY([Start Date]3 + 1, TODAY()) / Duration3 >= [% Complete]3, IF(AND(TODAY() >= [End Date]3 + 1, [% Complete]3 < 1), "Yellow", "Red"), "Green")

 

______________________________________________

You say you aren't getting yellow, so lets follow the path to where yellow should appear in your formula

If(Criteria,if(Criteria,"Yellow"

However, you are able to get to both false cases. This almost certainly means there is an issue with your second criteria. 

AND(TODAY() >= [End Date]3 + 1, [% Complete]3 < 1)

Try to make this and statement pop true by  editing the formula

Stevefc

Thanks for your quick response. I will check this out and let you know how I get on, thanks again.

Stevefc

I’ve now managed to get my status in the right order using the following,

=IF(AND(TODAY() >= [End Date]3 + 1, [% Complete]3 <= 1), "Red", IF(NETWORKDAY([Start Date]3 + 1, TODAY()) / Duration3 >= [% Complete]3, "Yellow", "Green"))

Thank you for all your help, much appreciated!