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

Find more posts tagged with

Formulas

Reports

Services

Conditional Formatting

Project Management

Comments

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