Help with Unparseable Error - IF/AND Formula for Calculating Health RYGG(Grey)

Stephanie Pollard

Hello, I've been trying different options to use the IF/AND formula to calculate the RYGG ball symbols for the child health of multiple tasks and have been unsuccessful. First I tried using the Planned End Date and % Complete columns:

=IF(Status@row = "Complete", "Blue", IF(Status@row = "Not Started", "Gray", IF(AND(Status@row = "In Progress", [Planned End Date]@row > TODAY(15)), "Green", "Ideal"), IF(AND(Status@row = "In Progress", [Planned End Date]@row < TODAY(5), "Yellow", "Review"), IF(AND(Status@row = "In Progress",([Planned End Date]@row < TODAY(), "Red", "Review")))))

and second I tried using the Status and Planned End Date columns:

=IF(Status@row = "Complete", "Blue", IF(Status@row = "Not Started", "Gray", IF(AND([Planned End Date]@row > TODAY(15), [% Complete]@row > 0.25), "Green", "Yellow", IF(AND([Planned End Date]@row < TODAY(5), [% Complete]@row < 0.65), "Yellow", "Green", IF(AND([Planned End Date]@row < TODAY(), [% Complete]@row < 0.9), "Red", "Yellow")))))

Any help is much appreciated!

Carson Penticuff

My apologies, I guess I missed a copy/paste.

=IF(Status@row = "Complete", "Blue", IF(Status@row = "Not Started", "Gray", IF(AND(Status@row = "In Progress", [Planned End Date]@row > TODAY(15)), "Green", IF(AND(Status@row = "In Progress", [Planned End Date]@row < TODAY()), "Red", IF(AND(Status@row = "In Progress", [Planned End Date]@row < TODAY(5)), "Yellow")))))

Carson Penticuff

Ok, so there are a few issues here to deal with.

• There are no symbol options with Green, Yellow, Red, Blue, and Gray. The options are Green, Red, Yellow, and Blue or Green, Red, Yellow, and Gray. Although I think this would be a nice suggestions to implement.
• I can't determine the function of "Ideal" and "Review" in your formula. Are they supposed to reference a different column, or are they supposed to be options in your [Status] column?
• Your "Red" and "Yellow" conditions will need to be swapped. Anything that returns true for less than today's date will also return true for less than five days from today, so less than today will need to be evaluated first.

Here is a modified version of your formula with "Ideal" and "Review" removed and the "Red" and "Yellow" conditions swapped. This formula WILL NOT work due to there being no option for both Blue and Gray. Hopefully it points you in the right direction though.

Stephanie Pollard

Hello Carson,

I apologize, I should have mentioned that I am using the text string "Blue" in place of the blue color ball, since there is not an option (yet) for all five options (RYGGB). The column is not restricted to only the symbols, so the Blue and Gray were working, but not the Red, Yellow or Green. I agree it would be great to have all and I will submit this as an suggestion.

The "Ideal" and "Review" values were what I thought was needed as the second condition in the function (meaning, if the condition was false, return those values).

Unfortunately I didn't see your suggested formula in your comment; would you be willing to re-post and I will try it?

Note: I went ahead and tried swapping the Red and Yellow conditions and I am still receiving an Unparseable error:

=IF(Status@row = "Complete", "Blue", IF(Status@row = "Not Started", "Gray", IF(AND(Status@row = "In Progress", [Planned End Date]@row > TODAY(15)), "Green"), IF(AND(Status@row = "In Progress", [Planned End Date]@row < TODAY(), "Red", IF(AND(Status@row = "In Progress", [Planned End Date]@row < TODAY(5), "Yellow"))))))

Note: The Gray values are not working with this formula, but they were before.

Thank you!

Carson Penticuff

My apologies, I guess I missed a copy/paste.

=IF(Status@row = "Complete", "Blue", IF(Status@row = "Not Started", "Gray", IF(AND(Status@row = "In Progress", [Planned End Date]@row > TODAY(15)), "Green", IF(AND(Status@row = "In Progress", [Planned End Date]@row < TODAY()), "Red", IF(AND(Status@row = "In Progress", [Planned End Date]@row < TODAY(5)), "Yellow")))))

Stephanie Pollard

Hi Carson, no problem. Thank you for the suggestions - the new formula works great!!