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

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

Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    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

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    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

  • Avinash Chandra
    Avinash Chandra ✭✭✭✭✭

    Hi Kelly,


    Thanks for your quick response. This worked for me.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!