# How to create Harvey balls to show progress of work

Options
edited 10/22/21

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.

• ✭✭✭✭✭
Options

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", "")))))
```

• ✭✭✭✭✭
Options

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", ""))))
```

• Options

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

• ✭✭✭✭✭
Options

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", "")))))
```
• Options

Works perfect! Thank you.

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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!