Harvey balls based on range of values in % completion

Britni Block

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?

 

 

Brett Evans

=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

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

BlkCrnc

Thanks!

JLB@ker

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