Harvey Ball Formula Tweak Needed

Matthew Bertucci
Matthew Bertucci ✭✭✭✭
edited 03/06/23 in Formulas and Functions

DISREGARD-I think I have it figured out (for now)...

I'm struggling with getting this formula to work correctly. I must have something reversed. Respectfully, requesting help. I’m over my head with this one.

=IF([Identified as Optimization Project]@row = 0, "Not REO", IF([Overall Project Status]@row = "Red", "Not Approved", IF([Overall Project Status]@row = "Black", "Completed", IF([Overall Project Status]@row = "Green", "Green", IF([Gate 2 (Review)]@row = 1, "Green", IF(AND(OR([Today's Date]@row < [Project Start Date]@row, ISDATE([Gate 2 (Review) End Date]@row), [Today's Date]@row < [Gate 2 (Review) End Date]@row - 5)), "Blue", IF(AND([Today's Date]@row >= [Gate 2 (Review) End Date]@row - 5, [Today's Date]@row <= [Gate 2 (Review) End Date]@row), "Yellow", IF([Today's Date]@row > [Gate 2 (Review) End Date]@row, "Red")))))))) 

The Gate 2 (Review) Status-should be yellow as we are within 5 days of the Gate (Review) End Date & the Gate 2 (Review) box is not checked. 

The Gate 3-5 Harvey Balls should be Blue not Yellow. The Overall Gate Health is accurate as being Yellow (since at least one ball is yellow).

 I can’t figure out what needs to be changed in the last part of the formula. I must have something reversed in the order of operations? Any help would be greatly appreciated.



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!