#### Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

# Harvey balls based on range of values in % completion

edited 12/09/19

I have a formula to use based on % completion of a project. I would like Harvey balls to show the status based on a range of values. It's not often that completion is exactly 25%, 50%, 75%, 100%

This is my formula but how do I figure the ranges?

=IF([% Complete]31 = 0, "Empty", IF([% Complete]31 < 0.25, "Quarter", IF([% Complete]31 = 0.5, "half", IF([% Complete]31 = 0.75, "ThreeQuarter", IF([% Complete]31 = 1, "Full")))))

Example of ranges:

0=empty

1-45%=Quarter

46-65% =Half

66-99%=ThreeQuarters

100%-Complete

is there a way?

If not, maybe another thought was to add checkboxes once it reaches the top value in each range?

Tags:

• ✭✭✭✭✭✭

=IF([% Complete]31 = 0, "Empty", IF([% Complete]31 < 0.45, "Quarter", IF([% Complete]31 < 0.65, "Half", IF([% Complete]31 = 0.99, "Three Quarter", IF([% Complete]31 = 1, "Full")))))

• ✭✭✭✭✭✭

A few minor corrections:

=IF([% Complete]31 = 0, "Empty", IF([% Complete]31 <= 0.45, "Quarter", IF([% Complete]31 <= 0.65, "Half", IF([% Complete]31 <= 0.99, "Three Quarter", IF([% Complete]31 <= 1, "Full")))))

If you can be sure your users won't set the [% Complete] to something greater than 100%, then you can get rid of one IF statement.

=IF([% Complete]31 = 0, "Empty", IF([% Complete]31 <= 0.45, "Quarter", IF([% Complete]31 <= 0.65, "Half", IF([% Complete]31 <= 0.99, "Three Quarter", "Full"))))

Craig

• Thanks!

• =IF([% Complete]31 = 0, "Empty", IF([% Complete]31 <= 0.45, "Quarter", IF([% Complete]31 <= 0.65, "Half", IF([% Complete]31 <= 0.99, "Three Quarter", "Full"))))

When I used this formula ... anything between .65 and .98 doesnt get the Three Quarter ball. .99 did give me the Three Quarter.

This discussion has been closed.