Formula involving multiple IF/AND statements

Dvdcffmn

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?

Mike Wilday

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"))))

Paul Newcome

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

Mike Wilday

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"))))

Paul Newcome