Fahim Shahriyar Dipto
Excel introduces diverse formulas to check if a value is between two numbers. Do you want to learn the formulas to check if the value is between two numbers?? If your answer is ‘yes’, this article is for you. Here we have discussed 5 easy and simple methods to do that.
Download Practice Workbook
Download the following practice workbook. It will help you to realize the topic more clearly.
5 Methods to Check If a Value Is Between Two Numbers in Excel
We have taken a dataset of students’ exam marks. We would like to know whose mark is between 40 to 60. Now we will use different formulas and methods to check if the value is between the two numbers.
Here, we have used Microsoft Excel 365 version, you may use any other version according to your convenience.
1. Using IF Function to Check If Value Is Between Two Numbers
We can use Excel IF function to check if the value is between two numbers. You need to follow the simple steps stated below.
📌 Steps:
- Select cell D5 and write the formula below.
=IF(C5>=$G$7,C5<=$G$6)
Here,
C5=Mark of the student.
G7=Lower side of the mark.
G6=upper side of the mark.
- Press ENTER.
The syntax =IF(C5>=$G$7,C5<=$G$6) checks if C5 lies between cell G6 & G7. If the logic is actual, it will return TRUE. Otherwise, it will return FALSE.
- Then drag down the Fill Handle tool for other cells.
- Finally, your result will look like the one below.
Read More: How to Check If Value Exists in Range in Excel (8 Ways)
2. Implementing AND Function to Find out Value Between Two Numbers
We can also use only AND function to check if a value is between two numbers. Follow the steps.
📌 Steps:
- Move to cell D5 and write down the formula.
=AND(C5>=$G$7, C5<=$G$6)
Here,
C5=Mark of the student.
G7=Lower side of the mark.
G6=upper side of the mark.
- Press ENTER.
The syntax =AND(C5>=$G$7,C5<=$G$6) checks if C5 lies between cells G6 and G7. If the logic is true, it will return TRUE. Otherwise, it will return FALSE.
- Drag down the formula for other cells.
- Finally, you will get your expected result.
Read More: How to Check If a Value is in List in Excel (10 Ways)
3. Using IF and AND Functions to Check If Value Is Between Two Numbers
We can combine Excel IF and AND functions to check if the value is between the two numbers.
📌 Steps:
- Initially, write down the formula in cell D5.
=IF(AND(C5>=$G$6,C5<=$G$5),”Yes”,”NO”)
C5=Mark of the student.
G7=Lower side of the mark.
G6=upper side of the mark.
- Press ENTER.
Formula Breakdown:
AND(C5>=$G$6,C5<=$G$5)→checks whether the value of C5 lies between cell G5 and G6.
IF(AND(C5>=$G$6,C5<=$G$5),”Yes”,”NO”)→If the logic is true then it will return YES and if the logic is not true it will return NO.
- Finally, drag down the formula for other cells and the result will look like the below.
Read More:How to Check If a Cell is Empty in Excel (7 Methods)
4. Using AND, MIN, and MAX Functions
In Excel, you can use a limit of maximum value and minimum value range and check if a value is between two numbers of the assigned minimum and maximum value. For this, you can use AND, MIN, and MAX functions.
📌 Steps:
- Firstly, select cell E5 and write the following formula.
=AND(D5>=MIN(B5,C5),D5<=MAX(B5,C5))
Here,
B5=Start Mark.
C5=End Mark.
D5=Obtained Mark.
- Press ENTER.
Formula Breakdown:
AND(D5>=MIN(B5,C5),D5<=MAX(B5,C5))→checks if D5 is between the minimum value of B5 and C5 and the maximum value of B5 and C5. If the logic is true, it will return to TRUE. Else, it will return FALSE.
- Finally, drag down and get the formula for other cells.
Read More: VBA to Check If Cell is Empty in Excel (5 Methods)
5. Applying Conditional Formatting
Excel has a built-in feature, Conditional Formatting to check if a value is between two numbers. This feature formats the cell with color to make you realize the difference in the result.
📌 Steps:
- First, go to cell D5 and write down the formula.
=AND(C5>=$G$7, C5<=$G$6)
- Press ENTER.
- Drag down Fill Handle for other cells.
- Then select the entire cell range D5:D11 >> go to HOME tab >> click Conditional Formatting >> select New Rule.
- A dialog box will appear. Click Use a formula to determine which cells to format.
- In the box, write the above formula and start format.
- A new dialog wizard will open. Select Fill >> Select a color >> Click OK.
- Then click Format.
- Finally, your result will look like the screenshot below.
Practice Section
We have provided a practice section on each sheet on the right side for your practice. Please do it by yourself.
Conclusion
That’s all about today’s session. And these are some easy methods to check if the value is between two numbers. Please let us know in the comments section if you have any questions or suggestions. For your better understanding, please download the Practice Workbook. Visit our website Exceldemy, a one-stop Excel solution provider, to find out diverse kinds of excel methods. Thanks for your patience in reading this article.
Related Articles
- Excel VBA: Check If a File Exists or Not
- [Fixed!] Spell Check Is Not Working in Excel (4 Solutions)
- How to Turn on Spell Check in Excel (3 Ways)
- Add Checkbox in Excel without Using Developer Tab (3 Methods)
- Excel VBA: Check If a Sheet Exists (2 Simple Methods)
- If Value Exists in Column Then TRUE in Excel
