#### 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

Options
edited 12/09/19

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…

Zoran

• Employee
edited 12/04/15
Options

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

This discussion has been closed.