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
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
145%=Quarter
4665% =Half
6699%=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?
Comments

=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.
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.4K Get Help
 364 Global Discussions
 202 Industry Talk
 430 Announcements
 4.4K Ideas & Feature Requests
 137 Brandfolder
 129 Just for fun
 128 Community Job Board
 446 Show & Tell
 28 Member Spotlight
 1 SmartStories
 284 Events
 35 Webinars
 7.3K Forum Archives