How to Check If Value Is Between 10 and 20 in Excel

Sometimes we need to check if the value is between 10 and 20 in Excel. We can see that Excel formulas and different features are made for us to solve these types of problems easily. In this article, we will know how to check if the value is between 10 and 20 in Excel with some easy methods and explanations.


Download Practice Workbook

Download the practice workbook from here.


5 Easy Methods to Check If Value Is Between 10 and 20 in Excel

Assuming we have a dataset (B4:D9) of a company’s sold items with their quantity. Now, we will check whether the quantity of each sold item is between 10 and 20. We will see the result in column D. Here in the dataset, cells G6 and G7 contain the Maximum (20) & Minimum (10) values respectively.

Excel If Between 10 and 20


1. Check If Value Is Between 10 and 20 Using IF Function

We know that the IF function helps us to run a logical test as well as returns TRUE for one value and FALSE for another one. We will use this function to get the value between 10 & 20.

Steps:

  • First, select Cell D5.
  • Now write down the formula:
=IF(C5>=$G$7,C5<=$G$6)
  • Then press Enter to see the result.

Check If Value Is Between 10 and 20 Using IF Function

  • Here, we will see a small green box at the bottom corner of Cell D5. If we hover a mouse on it, we can see that it turns into Black Plus (+) sign.
  • Next, left-click the mouse and drag down the plus sign.
  • After that, release the mouse.
  • Finally, we can see the result in column D.

Read More: If a Value Lies Between Two Numbers Then Return Expected Output in Excel


2. Insert AND Function to Find Out Value Between 10 and 20 in Excel

The AND function is one of the most frequently used functions in Excel. In this dataset, it will return TRUE if the values are between 10 & 20; otherwise FALSE.

Steps:

  • In the beginning, select Cell D5.
  • Next, type the formula:
=AND(C5>=$G$7,C5<=$G$6)
  • Now press Enter.

Insert AND Function to Find Out Value Between 10 and 20 in Exce

  • In the end, use the Fill Handle tool to see the result.


3. Detect Values Between 10 and 20 in Excel Using IF and AND Functions

In this method, we will use the IF function and the AND function to determine if the value is between 10 & 20. Let’s see how to do this.

Steps:

  • First, select Cell D5.
  • After that, write down the below formula:
=IF(AND(C5>=$G$7,C5<=$G$6),"Yes","No")
  • Then press Enter.

Detect Values Between 10 and 20 in Excel Using IF and AND Functions

Here, the AND function checks if the value is between 10 & 20 or not. Then the IF function returns Yes or NO, based on the logic.
  • Further, use the Fill Handle to autofill the below cells.


Similar Readings


4. Combination of MIN, MAX & AND Functions to Look If the Value Is Between 10 and 20

The MAX function returns the maximum value whereas the MIN function returns the minimum value of the given argument. Here, we can combine these two functions with the AND function to see if the value is between 10 & 20 or not.

Steps:

  • Select Cell D5.
  • Now, enter the below formula:
=AND(C5>=MIN($G$7,$G$6),C5<=MAX($G$7,$G$6))
  • After that, press Enter.

Combination of MIN, MAX & AND Functions to Look If the Value Is Between 10 and 20

  • Next, apply the Fill Handle tool to the below cells.

🔎 How Does the Formula Work?

  • C5>=MIN($G$7,$G$6): This will check whether the cell C5 is greater than or equal to the minimum value in the G6:G7 range and return FALSE.
  • C5<=MAX($G$7,$G$6): This will check whether the cell C5 is less than or equal to the maximum value in the G6:G7 range and return TRUE.
  • AND(C5>=MIN($G$7,$G$6),C5<=MAX($G$7,$G$6)): This part will evaluate the condition of the cell C5 and return FALSE.

5. Apply Conditional Formatting in Excel to Highlight Value Between 10 and 20

Excel Conditional Formatting helps us apply specific formatting to a range. Let’s see how to apply it to our dataset.

5.1 Use Formula

We will use the AND function with Conditional Formatting to highlight values between 10 & 20. Follow the below steps.

Steps:

  • First, select the range C5:C9 of quantity.
  • Next, go to the Home tab.
  • Select the Conditional Formatting drop-down.
  • Now select the New Rule.

Apply Conditional Formatting in Excel to Highlight Value Between 10 and 20

  • A New Formatting Rule window pops up. Go to Use a formula to determine which cells to format option.
  • In the formula box, type the formula:
=AND(C5>=$F$7,C5<=$F$6)
  • Select the Format option.

Use Formula

  • Further, from the Format Cells window, go to the Fill tab.
  • After that, select a background color.
  • Consequently, we can see the color preview from the Sample option.
  • Click on OK.

  • Again, click on OK.

  • Finally, we can see the result.


5.2 Apply Highlight Cells Rules Option

Excel has some built-in features to make the calculation easier. Conditional Formatting is one of them. We are going to follow the steps below.

Steps:

  • Select the range C5:C9.
  • Now go to the Home tab > Conditional Formatting drop-down.
  • Select the Highlight Cells Rules option.
  • Then click on the Between.

Apply Highlight Cells Rules Option

  • We can see a Between window pops up.
  • Further, input the numbers (10 & 20) in Format cells that are BETWEEN section.
  • Also, select any color. Here we have selected Yellow Fill with Dark Yellow Text option.
  • At last, click on OK.

  • Consequently, we can see that all the cells containing values between 10 & 20 are highlighted. See the screenshot below for a better understanding.


Similar Readings


How to Count Value Between 10 and 20 in Excel

Suppose, we want to count the cells that contain the value between 10 & 20 in Excel. We use different types of excel functions here to perform the job. Here, we used the same dataset as above.

1. Insert COUNTIFS Function

The COUNTIFS function counts the cells in an array or range that matches the multiple criteria. It is tagged under Excel Statistical functions. Let’s see how to apply this to our dataset.

Steps:

  • In the beginning, select Cell F8.
  • Further, enter the below formula:
=COUNTIFS(C5:C9,"<=20",C5:C9,">=10")
  • Next, hit Enter to see the result. In our case, it’s 2.

How to Count Value Between 10 and 20 in Excel

Here the COUNTIFS function searches for the cells that contain values between 10 & 20 in the C5:C9 range.

2. Use COUNTIF Function

The COUNTIF function counts the cells in an array or range that matches a single criterion. That means the exact use of the COUNTIF function accepts only one argument. Suppose, we have the same dataset as before. Now we are going to apply the COUNTIF function.

Steps:

  • First, select Cell F8.
  • After that, write the below formula:
=COUNTIF($C$5:$C$9,">=10")-COUNTIF($C$5:$C$9,">=20")
  • Then, hit Enter. We can see that the result is 2.

Use COUNTIF Function

In this formula, the COUNTIF function searches for the cells in the C5:C9 range that contain values between 10 & 20 and get the result 2.

Conclusion

Using these methods, we can quickly check if the value is between 10 and 20 in Excel. There is a practice workbook that we added. Go ahead and give it a try. Visit the ExcelDemy website for more articles like this. Feel free to ask anything or suggest any new methods.


Related Articles

Nuraida Kashmin

Nuraida Kashmin

Hi Everyone! I am Nuraida. Cordially welcome you to my profile. I am a Team Leader of Excel and VBA Content Developer in ExcelDemy. Here I will also post articles related to Microsoft Excel. With a strong educational background in Mechanical Engineering through experimental learning from Rajshahi University of Engineering & Technology. Skilled in Microsoft Word, Creative Writing, Microsoft Excel, Project Management, Support to Organize Different Events, Reporting, Monitoring & Documentation, Online Advocacy and Event Management Related to SAP and Youth Leaders.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo