# Formula involving multiple IF/AND statements

Options
edited 12/09/19

I need to modify the following formula to include additional AND conditions.

=IF(Finished12 = 1, "Blue", IF(Target12 >= TODAY(+14), "Green", IF(AND(Target12 >= TODAY(+7), Target12 < TODAY(+14, "Yellow", IF(AND(Target12 < TODAY(+7),%COMPLETE>70, "Red"))))

The additional condition would be  in the third if and would be Percent Complete is less than .5.

Any ideas?

• ✭✭✭✭✭✭
edited 02/13/19
Options

Try this. But I don't know what color it will be if your percentage complete is over .5... it might not be any color.

***Edited to revise formula****

=IF(Finished12 = 1, "Blue", IF(Target12 >= TODAY(+14), "Green", IF(AND(Target12 >= TODAY(+7), Target12 < TODAY(+14), [% Complete]12 <.5), "Yellow", IF(AND(Target12 < TODAY(+7),%COMPLETE>.7, "Red"))))

• ✭✭✭✭✭✭
Options

May want to change your Red criteria to "> .7" instead of "> 70".

• ✭✭✭✭✭✭
Options

Good catch Paul! the >70 by the %complete part. Here it is revised.

=IF(Finished12 = 1, "Blue", IF(Target12 >= TODAY(+14), "Green", IF(AND(Target12 >= TODAY(+7), Target12 < TODAY(+14), [% Complete]12 <.5), "Yellow", IF(AND(Target12 < TODAY(+7),%COMPLETE >.7), "Red"))))

• ✭✭✭✭✭✭
Options