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

Options
Britni Block
edited 12/09/19 in Archived 2017 Posts

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:

Comments

  • Brett Evans
    Brett Evans ✭✭✭✭✭✭
    Options

    =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")))))

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭
    Options

    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

  • JLB@ker
    Options

    =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.