Microsoft Excel has 6 logical operators which are also known as comparison operators. And Less Than or Equal to (<=) is one of them. The Less Than or Equal to (<=) is used to perform the various logical operations in Excel. It compares two values and checks if the first value is less than or equal to the second value and returns TRUE if the answer is yes otherwise FALSE. In this article, I’m going to explain how to use less than or equal to in Excel.
Download to Practice
8 Ways to Use Less Than Or Equal to Operator in Excel
In this section, I’m going to demonstrate several ways of using the Less Than or Equal to (<=) operator. To make the explanation clearer, I’m going to use a sample dataset that contains information about two fruit stores. The tables of the dataset have 3 columns: Fruit Name, Quantity, and Price.
1. Compare String Values with Less Than Or Equal to Operator
By using the Less Than or Equal to (<=) logical operator you can compare string values in Excel.
To start with, select any cell to place your resultant value.
➤ I selected the J6 cell.
⏩ In cell J6, type the following formula.
=B6<=F6
Here, the logical operator Less Than or Equal to (<=) is comparing the string value of cells B6 and F6. If the string value of the B6 cell is less than or equal to the F6 cell, then it will return TRUE otherwise FALSE.
⏩ Press the ENTER key and you will get the compared result of both cells if the condition is met then the result will be TRUE otherwise FALSE.
Here the string value of the B6 cell is equal to the F6 cell string value that’s why it returned TRUE as a result.
⏩ Now, you use the Fill Handle to AutoFill the formula for the rest of the cells.
Read More: How to Perform Greater than and Less than in Excel (5 Methods)
2. Compare Numeric Values with Less Than Or Equal to Operator
You also can use the Less Than or Equal to (<=) logical operator to compare numeric values in Excel.
Here, I’m using a dataset of two fruit stores given below. From there, I will compare the prices of the same fruit in both stores to know where the price is lower.
To start with, select any cell to place your resultant value.
➤ I selected the H6 cell.
⏩ In cell H6, type the following formula.
=C6<=F6
Here, the logical operator Less than or equal to (<=) is comparing the numeric value of cells C6 and F6. If the numeric value of the C6 cell is less than or equal to the F6 cell, then it will return TRUE otherwise FALSE.
⏩ Press the ENTER key and you will get the compared result of both cells if the condition is met then the result will be TRUE otherwise FALSE.
Here the numeric value of the C6 cell is greater than the F6 cell value that’s why it returned FALSE as a result.
⏩ Now, you use the Fill Handle to AutoFill the formula for the rest of the cells.
Read More: How to Use Greater Than or Equal to Operator in Excel Formula
3. Compare Dates with Less Than Or Equal to Operator in Excel
The Less Than or Equal to (<=) logical operator also compares the date values.
Here, I added an extra column in the dataset which is Order Date to show you the comparison of date values by using the Less Than or Equal to (<=) logical operator.
To begin with, select any cell to place your resultant value.
➤ I selected the J6 cell.
⏩ In cell J6, type the following formula.
=D6<=H6
Here, the logical operator Less Than or Equal to (<=) is comparing the date value of the D6 and H6 cells. If the date value of the D6 cell is less than or equal to the H6 cell, then it will return TRUE otherwise FALSE.
⏩ Press the ENTER key and you will get the compared result of both cells if the condition is met then the result will be TRUE otherwise FALSE.
Here the date value of the D6 cell is newer (greater) than the H6 cell value that’s why it returned FALSE as a result.
⏩ Now, you use the Fill Handle to AutoFill the formula for the rest of the cells.
Read More: ‘Not Equal to’ Operator in Excel (With 5 Examples)
4. Compare Dates Using DATEVALUE with Less Than Or Equal to Operator
Though the Less Than or Equal to (<=) logical operator compares the date values, it will show an error if you compare the dates directly without using cell reference. To avoid this error you can use the DATEVALUE function with the Less Than or Equal to (<=) logical operator.
Let me show you when an error may occur.
To begin with, select any cell to place your resultant value.
➤ I selected the J6 cell.
⏩ In cell J6, type the following formula.
=D6<=1/30/2022
Here, the logical operator Less Than or Equal to (<=) is comparing the date value of cell D6 with the date 1/30/2022. If the date value of the D6 cell is less than or equal to the date 1/30/2022 then it will return TRUE otherwise FALSE.
⏩ Press the ENTER key and you will get the compared result D6 cell and 1/30/2022. If the condition is met then the result will be TRUE otherwise FALSE.
Here the date value of the D6 cell is less than the 1/30/2022 date so the result was supposed to be TRUE, but it is showing FALSE which is not correct.
To overcome such types of problems you can use the DATEVALUE function with the Less Than or Equal to (<=) logical operator.
To begin with, select any cell to place your resultant value.
➤ I selected the J6 cell.
⏩ In cell J6, type the following formula.
=D6 <= DATEVALUE("1/30/2022")
Here, in the DATEVALUE function, I used the date “1/30/2022” as date_text. Now, the DATEVALUE function will convert this date into a number which is 44591.
Now, the logical operator Less Than or Equal to (<=) will compare the date value of the D6 cell with the returned number from the DATEVALUE function. If the date value of the D6 cell is less than or equal to the number, then it will return TRUE otherwise FALSE.
⏩ Press the ENTER key and you will get the compared result of the D6 cell and the date_text “1/30/2022”. If the condition is met, then the result will be TRUE otherwise FALSE.
⏩ You will need to insert each date as date_text in the DATEVALUE formula.
Read More: Reference Operator in Excel [Basics + Special Uses]
5. Using Less Than Or Equal to with IF Function in Excel
The logical operators are widely used with the IF, SUMIF, and COUNTIF functions in Excel.
You can use the Less Than or Equal to (<=) logical operator with the IF function to compare particular values and return your expected result except TRUE or FALSE.
Here, I’m going to use the dataset given below to use the Less Than or Equal to (<=) operator with the IF function. I will use the (<=) logical operator within the logical_test parameter to find out who failed or passed based on numbers.
To start with, select any cell to place your resultant value.
➤ I selected the E4 cell.
⏩ In cell E4, type the following formula.
=IF(D4<=59, "Fail","Pass")
Here, in the IF function, I used D4<=59 as logical_test, provided the text “Fail” as value_if_true and “Pass” as value_if_false.
In the logical_test I used the Less Than or Equal to (<=) operator to find the numbers which are less than or equal to 59. If the condition is met, then it will return Fail otherwise Pass.
⏩ Press the ENTER key and you will get who failed or passed depending on the number.
Here, the value of D6 cell is greater than the value 59 which means the IF function will return the text of the value_if_false argument which is Pass.
⏩ Now, you use the Fill Handle to AutoFill the formula for the rest of the cells.
Read More: How to Apply ‘If Greater Than’ Condition In Excel (9 Ways)
6. COUNTIF Function with Less Than Or Equal to Operator
You also can use the Less Than or Equal to (<=) logical operator with the COUNTIF function to find particular values and return the count of the values based on criteria.
Here, I’m going to use the dataset given below to use the Less Than or Equal to (<=) operator with the COUNTIF function. I will use the (<=) logical operator as criteria to find out who failed on the selected range.
To start with, select any cell to place your resultant value.
➤ I selected the H4 cell.
⏩ In cell H4, type the following formula.
=COUNTIF(D4:D11, "<=59")
Here, in the COUNTIF function, I selected the range D4:D11 as a range and “<=59” as criteria.
Now, the COUNTIF function will search the values where the numbers are Less Than or Equal to 59.
⏩ Press the ENTER key and you will get the count of the value Fail.
7. Using Less Than Or Equal to with SUMIF Function in Excel
In case you want to SUM particular values depending on any criteria then you can use the Less Than or Equal to (<=) logical operator with the SUMIF function.
To demonstrate the procedure, I’m going to use a dataset given below that represent sales information of the different region.
To begin with, select any cell to place your resultant value.
➤ I selected the G4 cell.
⏩ In cell G4, type the following formula.
=SUMIF(D4:D12, "<=6000",D4:D12)
Here, in the SUMIF function, I selected the range D4:D12 as range, used the condition “<=6000” as criteria then selected the cell range D4:D12 as sum_range.
Now, the SUMIF will look for the values where it is Less Than or Equal to 6000 and will sum up the total of those values where criteria are fulfilled.
⏩ Press the ENTER key and you will get the total sum of the values that are less than 6000.
8. Conditional Formatting Using Less Than Or Equal to Operation
You also can use the Less Than or Equal to (<=) logical operator in the Conditional Formatting to Highlight particular values.
For the demonstration purpose, I’ve taken a dataset given below that contains numbers of particular students in a particular subject. From there, I will Highlight the values that are Less Than or Equal to 59.
To begin with, select the cell range from where you want to Highlight the cell.
⏩ I selected the cell range D4:D11.
➤ Open the Home tab >> from Conditional Formatting >> select New Rule
A dialog box will pop up. From there select any rule from Select a Rule Type.
⏩ I selected the rule Use a formula to determine which cells to format.
In Edit the Rule Description, type the following formula.
=D4 <= 59
➤ Click on Format to set the Fill color.
Another dialog box will pop up. From there select the fill color of your choice.
⏩ I selected the color fill color Red.
Then, click OK.
As all the New Formatting Rule is selected finally click OK again.
➤ Therefore, where the condition is met it is Highlighted with Red color.
Practice Section
I’ve provided a practice sheet in the workbook to practice these explained examples.
Conclusion
In this article, I have explained 8 ways to use less than or equal to Excel. I’ve also given some examples of how you can use this operator with various functions. Last but not least, if you have any kind of suggestions, ideas, or feedback please feel free to comment down below.