Formula involving multiple IF/AND statements
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?
Comments
-
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"))))
-
May want to change your Red criteria to "> .7" instead of "> 70".
-
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"))))
