Archived 2015 Posts
Archived 2015 Posts
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.
How to use a range of numbers in a formula
Hi SmartUsers,
I have a formula to show bars according to the completion % of a cell. The formula is:
=IF(Completed?9 = 1, "Full", IF([% Complete]9 = 0.25, "Quarter", IF([% Complete]9 = 0.5, "Half", IF([% Complete]9 = 0.75, "Three Quarter", IF([% Complete]9 = 1, "Full", "Empty")))))
It'll show 1 bar if a task is 25%, 2 bars if 50% done, etc. However, 20% will show no bars at all; obviously.
Best thing I came up with is to try and trap all possible iterations but the nested IF statements would even scare Linus Torvalds away.
Question: Is there a way to use a range to test again? Something like, if cell is between 0 and 25% show quarter full, if 25% to 50% then show half full, etc.?
Solution is probably amazingly obvious but I can’t figure it out…
Thank you all in advance!
Zoran
Comments
-
Hi Zoran!
You are close! It is possible to use < and > in formulas which will get you the results you are looking for. Try this:
=IF(Completed?9 = 1, "Full", IF([% Complete]9 < 0.25, "Empty", IF([% Complete]9 < 0.5, "Quarter", IF([% Complete]9 < 0.75, "Half", IF([% Complete]9 < 1, "Three Quarter", IF([% Complete]9 = 1, "Full", "Empty"))))))
0%-24% = Empty
25%-49% = Quarter
50%-74% = Half
75%-99% = Three Quarter
100% = Full
