# IF / OR formula help

Options
edited 12/09/19

Hi community,

hope somebody may be able to help.

I am trying to link a % of completion column with a progress bar. Formula is as follows:

=IF([% complete]1 = "0", "Empty", IF(OR([% complete]1 > "0", [% complete]1 <= "0.25"), "Quarter", IF(OR([% complete]1 > "0.25", [% complete]1 <= "0.5"), "Half", IF(OR([% complete]1 > "0.5", [% complete]1 <= "0.75", "Three Quarter"), IF(OR([% complete]1 > "0.75", [% complete]1 <= "1", "Full"))))))

It only correctly returns "Empty"results, it also returns "Quarter" although incorrectly as it applies "Quarter" to all results diverse from zero.

Alex

• ✭✭✭✭✭✭
Options

=IF([% complete]1 = 0, "Empty", IF( [% complete]1 <= .25, "Quarter", IF( [% complete]1 <= 0.5, "Half", IF([% complete]1 <= .75, "Three Quarter", "Full"

Give that a once over and a try.

• Options

Thank you for your response, I eventually came up with a solution that actually works:

=IF([% complete]1 = "0", "Empty", IF(AND([% complete]1 > "0", [% complete]1 <= "0.25"), "Quarter", IF(AND([% complete]1 > "0.25", [% complete]1 <= "0.5"), "Half", IF(AND([% complete]1 > "0.5", [% complete]1 <= "0.75"), "Three Quarter", IF(AND([% complete]1 > "0.75", [% complete]1 <= "1"), "Full")))))

• ✭✭✭✭✭✭
Options

L@123's formula should work. But for teaching purposes, your original formula had an OR statement for the Quarter that stated IF the %complete was greater than 0 OR less than .25. It could have been written with an AND instead of OR to make that work. Once the formula saw it was over 0 (any value) it would apply the quarter to everything over 0. IF formulas always stop at the first true statement it finds. Your quarter statement was true for every value over 0.

BUT

L@123's works as well (and is less complicated to write) because it first looks at the value and determines if its zero. Ifi its true, it puts "empty", then it looks for anything that is less than .25 (0's have already been dealt with) then it looks for anything that is less than half, and so forth and so on. You don't need the range of values because the IF statement already processed those cases before moving on to the 2nd and 3rd statements.

Hope that helps explain what is going on.

• Options

Thank you Mike, appreciate your feedback

• ✭✭✭✭✭✭
Options

You're welcome!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!