Excel IF Statement Between Two Numbers (4 Ideal Examples)

Get FREE Advanced Excel Exercises with Solutions!

In this article, we will show 4 ideal examples of how to use if statement between two numbers in Excel. To check if a number is within 2 boundary ranges, we can use the IF and AND functions. You can easily do this task in Excel. So, without any delay, let’s start the discussion.


4 Ideal Examples of IF Statement Between Two Numbers in Excel

To demonstrate the methods, we will use the following dataset. The dataset contains information on the employees of a company. Here, there is information about Employee’s Name, Age and Joining Dates. Now, we will show 4 ideal examples of Excel IF statement between two numbers.

Dataset for showing the use of if statements between two numbers in excel


1. Use IF Statement Between Two Numbers in Excel

In this example, with the AND Function, we can find out if the age of the employees is inside 2 boundary numbers. And with the IF Function, we can add writing to the conditions. Let’s follow the steps below to learn the method.


1.1. Use IF Statement Between Two Numbers Exclusively

  • Firstly, declare the boundary numbers.
  • Here, we have taken 32 and 26 as Upper Limit and Lower Limit respectively.
  • So, we will find out if the age is between this boundary condition.

Declare Upper and Lower Limits of Boundary

  • To do so, write the following formula in the formula bar of the D5 cell:
=IF(AND(C5>$C$16,C5<$C$15),"Yes","No")
  • Then, press Enter to see the result.
  • Here, this No result indicates that the value is not between the limits.

Formula for using IF Statement between dates

Here, if the value of the C5 cell( Age of the employee) is greater than the C16 cell (Lower Limit) and less than the C15 cell (Upper Limit), then it will show Yes. Else, it will show No.
  • After that, drag down the formula in the D6:D13 range with the Fill Handle option.

Drag Down formula of if statement in excel between two numbers in following cells

  • As a result, you can observe the result in the column.
  • Here, if the age is between 26 and 32 exclusively, then it is showing Yes.
  • Else, the result is showing No.

Result of if statement in excel between two numbers exclusively

Read More: How to Use IF Statement with Yes or No in Excel


1.2. Use IF Statement Between Two Numbers Inclusively

  • In the following step, we will find out if the age is between this boundary condition with the Inclusive condition.
  • Now, write down the following formula in the formula bar of the E5 cell:
=IF(AND(C5>=$C$16,C5<=$C$15),"Yes","No")
  • Then, press Enter to see the result.
  • Here, this Yes indicates that the value is between the limits.

Formula for IF statement between two numbers in Excel

Here, if the value of the C5 cell( Age of the employee) is greater or equal to the C16 cell (Lower Limit) and less or equal to the C15 cell (Upper Limit), then it will show Yes. Else, it will show No.
  • After that, drag down the formula in the E6:E13 range with the Fill Handle option.

Drag Down formula of if statement excel between two numbers in following cells

  • As a result, you can observe the result in the column.
  • Here, if the age is between 26 and 32 inclusively, then it is showing Yes.
  • Else, the result is showing No.

Result of if statement excel between two numbers inclusively

Here, the numbers between 26 and 32 is showing Yes. For the Inclusive option, 26 and 32 are also showing Yes and for the Exclusive option, 26 and 32 are showing No.

Read More: How to Use IF Function with OR and AND Statement in Excel


2. Check If Value Is Between Maximum and Minimum Numbers

In this example, we will find out if the age of the employees is inside 2 boundary numbers. Let’s follow the steps below to learn the method.


2.1. Consider Exclusive Result

  • Firstly, declare the boundary numbers.
  • Here, we have taken 35,38, and 34 as Upper Limits.
  • And, we have taken 28,27, and 26 as Lower Limits.
  • From these limits, we have to calculate the Upper Limit and Lower Limit respectively.
  • After that, we will find out if the age is between the calculated boundary condition.

Declare Upper and Lower Limits of Boundary

  • To do so, write the following formula in the formula bar of the D5 cell:
=IF(AND(C5>MIN($C$16:$E$16),C5<MAX($C$15:$E$15)),"Yes","No")
  • Then, press Enter to see the result.
  • Here, this No result indicates that the value is not between the limits.

Formula of IF Statement to check If Value Is Between Maximum and Minimum Numbers

Here, using the MIN function we will get the Lower Limit of the C16:E16 range and by the MAX function, we will get the Upper Limit of the C15:E15 range. After that, if the value of the C5 cell( Age of the employee) is greater than the calculated Lower Limit and less than the calculated Upper Limit, then it will show Yes. Else, it will show No.
  • After that, drag down the formula in the D6:D13 range with the Fill Handle option.

Drag Down formula of if statement

  • As a result, you can observe the result in the column.
  • Here, if the age is between the calculated upper and lower range exclusively, then it is showing Yes.
  • Else, the result is showing No.

Result after Checking If Value is Between Maximum and Minimum Numbers Exclusively

Read More: How to Use If Then Else Statement in Excel VBA


2.2. Find Inclusive Result

  • In the following step, we will find out if the age is between this boundary condition with the Inclusive condition.
  • Now, write down the following formula in the E5 cell:
=IF(AND(C5>=MIN($C$16:$E$16),C5<=MAX($C$15:$E$15)),"Yes","No")
  • Then, press Enter to see the result.
  • Here, this Yes result indicates that the value is between the limits.

Formula for checking values between maximum and minimum

Here, using the MIN function we will get the Lower Limit of the C16:E16 range and by the MAX function, we will get the Upper Limit of the C15:E15 range. After that, if the value of the C5 cell( Age of the employee) is equal or greater than the calculated Lower Limit and equal or less than the calculated Upper Limit, then it will show Yes. Else, it will show No.
  • After that, drag down the formula in the E6:E13 range with the Fill Handle option.

Drag Down formula of if statement

  • As a result, you can observe the result in the column.
  • Here, if the age is between the calculated upper and lower range inclusively, then it is showing Yes.
  • Else, the result is showing No.

Result after Checking If Value is Between Maximum and Minimum Numbers Inclusively

Read More: Show Cell Only If Value Is Greater Than 0 in Excel


3. Insert IF Statement Between Two Dates in Excel

In this example, we will find out if the joining date of the employees is inside 2 boundary dates. Let’s follow the steps below to learn the method.

STEPS:

  • Firstly, declare the boundary numbers.
  • Here, we have taken the 1st of January, 2022 and 31st of December, 2022 as Lower Limit and Upper Limit respectively.
  • So, we will find out if the joining date is between this boundary condition.
  • To do so, write the following formula in the formula bar of the D5 cell:
=IF(AND(C5>=$C$16,C5<=$C$15),"Yes","No")
  • Then, press Enter to see the result.

Formula for IF statement between two dates

Here, if the value of the C5 cell( Joining Date of the employee) is greater or equal to the C16 cell (Lower Limit) and less or equal to the C15 cell (Upper Limit), then it will show Yes. Else, it will show No.
  • After that, drag down the formula in the D6:D13 range with the Fill Handle option.

Drag Down formula of if statement

  • As a result, you can observe the result in the column.
  • Here, if the joining date is between 1st January and 31 December 2022 then it is showing Yes.
  • Else, the result is showing No.

Result after Inserting IF Statement Between Two Dates

Read More: How to Use IF Statement with Not Equal To Operator in Excel


4. Apply IF Statement to Check If Dates Are Within Some Specific Days

In this example, we will find out if the joining date of the employees is within some specific days.


4.1. Within Next N Days

Suppose, we want to find out if there is any possibility of a new joining within a few days. Therefore, in the following step, we will find out if the joining date of the employees is within the next some days.

  • Now, write down the following formula in the formula bar of the D5 cell:
=IF(AND(C5>TODAY(),C5<=TODAY()+7),"Upcoming Joining","Already Joined")
  • Then, press Enter to see the result.
  • Today is 11th January 2023.
  • And, as the date is not between 11 to 18th January 2023, it is showing Already Joined.

Formula for checking if dates are within next n days

  • After that, drag down the formula in the D6:D13 range with the Fill Handle option.

Drag Down of IF statement formula

  • As a result, you can observe the result in the column.
  • Here, if the joining date is between today and (today+7) days, then it is showing Upcoming Joining.
  • Else, the result is showing Already Joined.

Result after Applying IF Statement to Check If Dates Are Within Next N Days


4.2. Within Last N Days

Suppose, we want to find out if there is any possibility of new joining within a few days. Therefore, in the following step, we will find out if the joining date of the employees is within the next some days.

  • Now, write down the following formula in the formula bar of the D5 cell:
=IF(AND(C5>TODAY(),C5<=TODAY()+7),"Upcoming Joining","Already Joined")
  • Then, press Enter to see the result.
  • Today is 11th January 2023.
  • And, as the date is not between 11 to 4th January 2023, it is showing No.

Formula for checking last n days

  • After that, drag down the formula in the D6:D13 range with the Fill Handle option.

Drag Down of formula

  • As a result, you can observe the result in the column.
  • Here, if the joining date is between today and (today7) days, then it is showing Recently Joined.
  • Else, the result is showing No.

 

Result after Applying IF Statement to Check If Dates Are Within Last N Days


Download Practice Workbook

To practice by yourself, download the following workbook.


Conclusion

In this article, we have demonstrated 4 examples of Excel IF statement between two numbers. There is a practice workbook at the beginning of the article. Go ahead and give it a try. Last but not least, please use the comment section below to post any questions or make any suggestions you might have.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Sudipta Chandra Sarker
Sudipta Chandra Sarker

Hello! Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel. Here I will be posting articles related to this. My educational degree is BSc in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology, Bangladesh. I have a great interest in research and development. I always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo