How to use a range of numbers in a formula

Zoran Grbic

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

Travis

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