nested forumlas

Paulina Raygoza

Hello, 

 

I'm having trouble with my nested formula and activating the Red, Green, Gray, Yellow ball based on the % compelte column. 

 

=IF([% Complete] = IS BLANK, activate the RED ball

=IF([% Complete] = less than 100%, activate the YELLOW ball

=IF([% Complete] = 100% activate the GREEN ball

=IF([% Complete] = 0% activate the GRAY ball

 

Addtionally, if it is past the deadline and not green activated can it automatically activiate the Red flag symbol? 

 

J. Craig Williams

Here you go:

 

=IF([% Complete]2 = 1, "Green", IF(AND(ISDATE([End Date]2), [End Date]2 < TODAY()), "Red", IF(ISBLANK([% Complete]2), "Red", IF([% Complete]2 = 0, "Gray", IF([% Complete]2 > 0, "Yellow", "other")))))

 

or formatted for legibility:

=IF([% Complete]2 = 1, "Green",

  IF(AND(ISDATE([End Date]2), [End Date]2 < TODAY()), "Red",

  IF(ISBLANK([% Complete]2), "Red",

  IF([% Complete]2 = 0, "Gray", IF([% Complete]2 > 0, "Yellow", "other")))))

 

Here's the results:

 

 

Tips for nested If's:

IF's are processed left to right - when the result is true, the false branch is not checked or processed (and the reverse is true).

Watch out for satisfying the check before you intend to -- like checking for less than 100% and then checking for 0%. 0 is less than 100 so it won't get there.

(That's why we check for gray before we check for yellow, and why we check for a past due date before we care about what value other than 100% it might be)

 

Enjoy

 

Craig