How to identify if a number falls between two other numbers
Hello,
I am trying to determine how to capture if a number falls between two other numbers. The numbers vary on each row. Example:
=IF([Column22]@row >= 9, "1", IF([Column23]@row >= 9, "1"))
Column 22 is 4 and Column 23 is 7. It works find here, but I also need this formula to provide a "0" or blank for if the number is less than 4. Example:
=IF([Column22]@row >= 3, "1", IF([Column23]@row >= 3, "1"))
How can I make the formula understand the OR piece of this formula?
Answers
-
I want to make sure I understand your problem. Are you trying to return a "1" if the value is greater than or equal to 4 and less than or equal to 9, and a "0" if it falls outside that range?
John
-
@John Shane Yes, exactly.
-
This formula will evaluate the four criteria and return a 0 if any are true. If all are false (number is between 4 and 9), it will return a 1. Hope this helps.
=IF(OR([Column22]@row > 9, [Column23]@row > 9, [Column22]@row < 4, [Column23]@row < 4), 0, 1)
Cheers,
John
-
@John Shane Thank you! That works.
Now, if I need to know if certain numbers fall within that range, can I do that? Example, I need to know if 3 falls between the numbers in Column 22 and Column 23 or if 8 falls between the numbers in Column 22 or Column 23. In the snippet below, I want a 1 in the columns under 3, 4, 5, 6, etc if they that number falls between the range of numbers in Column 22:Column 23.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!