Formula not working...need to eliminate N/A then determine Health bubble.

sgodown106626

Hi All,

This formula is not working. The goal is to eliminate the N/A lines based on Status and then assign Health color based on looking at %Complete.

=IF((Status@row = "N/A", "", [% Complete]@row = 1, "Blue", IF(AND([% Complete]@row = 0, [Start Date]@row < TODAY()), "Yellow", IF(OR([Start Date]@row = "", AND([Start Date]@row > TODAY(), [% Complete]@row = 0)), "", IF(OR([End Date]@row = "", [End Date]@row > TODAY()), "Green", IF([End Date]@row < TODAY(), "Red"))))))

Any de-bug help would be greatly appreciated!

Genevieve P.

Hi @sgodown106626

It looks like you're just missing one "IF(" in front of your second statement!

=IF(Status@row = "N/A", "", IF([% Complete]@row = 1,

Try this:

=IF(Status@row = "N/A", "", IF([% Complete]@row = 1, "Blue", IF(AND([% Complete]@row = 0, [Start Date]@row < TODAY()), "Yellow", IF(OR([Start Date]@row = "", AND([Start Date]@row > TODAY(), [% Complete]@row = 0)), "", IF(OR([End Date]@row = "", [End Date]@row > TODAY()), "Green", IF([End Date]@row < TODAY(), "Red"))))))

Cheers,

Genevieve