IF / OR formula help

Alessandro Terranova

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.

 

Thank you in advance

 

Alex

L_123

=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.

Alessandro Terranova

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")))))

Mike Wilday

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. 

Alessandro Terranova

Thank you Mike, appreciate your feedback

Mike Wilday

You're welcome!