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
