How to create Harvey balls to show progress of work

Shile

Hello,

Please I need help creating Harvey balls that will

show “Green” when work progress is FULL

show “Red” when work progress is NOT FULL and End Date is less than TODAY

show “Yellow” when work progress is less than FULL and NOT EMPTY

show “Gray” when work progress is EMPTY and End Date is BLANK 

Would also like the Harvey balls to roll up in the summary rows.

Thank you.

Parker Oxford

@Shile

Ah sorry, I thought I tested all the cases, I've accounted for it in this formula (below). If any other case doesn't work please let me know.

=IF(AND([work progress]@row = "", [End Date]@row = ""), "Gray", IF([work progress]@row = "Full", "Green", IF(AND([work progress]@row <> "Full", [End Date]@row < TODAY()), "Red", IF(AND([work progress]@row <> "Full", [work progress]@row <> "Empty"), "Yellow", IF(AND([work progress]@row <> "Full", [work progress]@row <> "Empty"), "Yellow", "")))))

Parker Oxford

Formula below, I matched your column names so let me know if it doesn't immediately apply.

=IF([work progress]@row = "Full", "Green", IF(AND([work progress]@row <> "Full", [End Date]@row < TODAY()), "Red", IF(AND([work progress]@row <> "Full", [work progress]@row <> "Empty"), "Yellow", IF(AND([work progress]@row <> "Full", [work progress]@row <> "Empty"), "Yellow", ""))))

Shile

Thank you for taking the time.

Every other formula worked , except show “Gray” when work progress is EMPTY and End Date is BLANK. It gives Red Harvey ball instead.

I highlighted my preferred outcome in green versus what the formula spills out, in the illustration below. 

I hope you can help. Thanks

Parker Oxford

@Shile

Ah sorry, I thought I tested all the cases, I've accounted for it in this formula (below). If any other case doesn't work please let me know.

=IF(AND([work progress]@row = "", [End Date]@row = ""), "Gray", IF([work progress]@row = "Full", "Green", IF(AND([work progress]@row <> "Full", [End Date]@row < TODAY()), "Red", IF(AND([work progress]@row <> "Full", [work progress]@row <> "Empty"), "Yellow", IF(AND([work progress]@row <> "Full", [work progress]@row <> "Empty"), "Yellow", "")))))

Shile

Works perfect! Thank you.

Only modification was including "Gray" in " " at the end. Nice!