Using OR/And Function

Avinash Chandra

I am trying to create a formula for the following:

45 days before the deadline – where work is completed less than 65% - Red alert

65-75 % - 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

Kelly Moore

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

Kelly Moore

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

Avinash Chandra

Hi Kelly,

Thanks for your quick response. This worked for me.