Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

Harvey Ball Formula Tweak Needed

✭✭✭✭
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.

 

image.png image.png


Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions