IF / OR formula help
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
Comments
-
=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.
-
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")))))
-
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.
-
Thank you Mike, appreciate your feedback
-
You're welcome!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!