Using OR/And Function
I am trying to create a formula for the following:
45 days before the deadline – where work is completed less than 65%  Red alert
6575 %  Yellow Alert
75% more – Green Alert
I am not able to combine two Parameters to determine the colours.
See an example
=IF(OR(AND([Revised Planned Finish]@row  [Revised Planned Finish]9 > 45, [% Complete]9 > 0.75, "Green")))
I keep on getting #incorrect argument set
Kindly advise
Best Answer

Hey @Avinash Chandra
=IF(AND([Revised Planned Finish]@row  [Revised Planned Finish]9 < 45, [% Complete]9<0.65), "Red", IF(AND([Revised Planned Finish]@row  [Revised Planned Finish]9 < 45, [% Complete]9<0.75, "Yellow", IF(OR(AND([Revised Planned Finish]@row  [Revised Planned Finish]9 < 45, [% Complete]9>=0.75), [% Complete]9=1), "Green")
Note the only OR that is needed is the one I added in your Green criteria. I assumed it would be Green if your %Complete went to 100% at any time.
I swapped you greater than 45 days to less than 45 days  I believe this is what you meant. I also noticed you called out a specific row number which will prevent you from turning this into a column formula. Depending on your data layout, you may be able to find workarounds that allow you to use a column formula.
Will the formula above work for you?
Kelly
Answers

Hey @Avinash Chandra
=IF(AND([Revised Planned Finish]@row  [Revised Planned Finish]9 < 45, [% Complete]9<0.65), "Red", IF(AND([Revised Planned Finish]@row  [Revised Planned Finish]9 < 45, [% Complete]9<0.75, "Yellow", IF(OR(AND([Revised Planned Finish]@row  [Revised Planned Finish]9 < 45, [% Complete]9>=0.75), [% Complete]9=1), "Green")
Note the only OR that is needed is the one I added in your Green criteria. I assumed it would be Green if your %Complete went to 100% at any time.
I swapped you greater than 45 days to less than 45 days  I believe this is what you meant. I also noticed you called out a specific row number which will prevent you from turning this into a column formula. Depending on your data layout, you may be able to find workarounds that allow you to use a column formula.
Will the formula above work for you?
Kelly

Hi Kelly,
Thanks for your quick response. This worked for me.
Help Article Resources
Categories
Check out the Formula Handbook template!