Microsoft Excel is a very powerful spreadsheet tool that can not only take up tabular data but can do a lot more than you can think of. You can use formulas, shortcuts, macro, VLOOKUP and many more.

In this guide, we will discuss how to use the less than or equal to the operator in Microsoft Excel.

The ‘Less than or Equal to’ operator (<=) is the logical operator and is known as the comparison operator. This is usually used in Excel to compare the values. There are a total of 6 different logical operators for now we will talk about less than or equal to operator.

It checks if the first value is less than or equal to the second value and then returns the answer as True or False depending on the condition. As it is a Boolean expression so, it can only return the answer of either True or False.

In most cases, the ‘less than or equal to’ operator is used to perform various logical operations in Microsoft Excel. You won’t see it being used alone and it is sometimes combined with other Excel operators and functions like IF, OR, NOT, SUMIF and COUNTIF.

Together with all these functions and operators, you can perform multiple calculations at once. Below we have a tutorial on how to use the ‘Less than or equal to’ operator with text, date, numbers and with other Excel functions.

Compare text Values with ‘<=’ Operator in Excel

As discussed earlier, the ‘less than or equal to’ operator can be used to compare two values in Excel, you should also know that the logical operators are not case-sensitive. This means while comparing two values the operators ignore the case differences.

Also, you should keep in mind that MS Excel considers the first alphabet ‘a’ as the smallest value and the last alphabet ‘z’ as the largest value while using these logical operators to compare the text strings. This means a<d, and m<l. To explain this, below we have an example.

First example- To check the text value in the cell A3 is less than or equal to the value in the cell B3, we can use the formula:

=A3<=B3

Do note that every formula in Excel should always start with an equal sign ‘=’. In the above formula, the first argument is cell C2 and the second argument is cell A4. Between them, we have used the logical operator less than or equal to. Since both the values are the same, hence the result is ‘TRUE’.

Alternatively, you can also use the direct text value instead of using the cell references. So, when you enter a text value inside a formula, make sure to use double quotation marks. below we have an example.

="Ant"<="ant"

As we have told you earlier that the logical operators are case insensitive, so it ignores the case differences and returns the answer is TRUE.

Example 2- Below we have written down some texts in two different cells of Microsoft Excel. In cell A3 we have the string ‘Ant’ while in cell B3 we have ‘Elephant. The formula we will use is ‘=A3<=B3’. it will return the value as ‘TRUE’. This is because Excel considers the letters which come afterwards are larger. Since the first letter, A of ant is smaller than E of elephant according to Excel so the operator will return the value as TRUE.

Example 3- If the first letter of both the strings is the same, then Excel automatically moves to the second letter of the strings, for instance, if we try to compare the words Ant and Ape from two different cells and use the formula as ‘=A3<=B3’. Then Excel will compare the letter ‘n’ and ‘p’. As the letter ‘p’ is not less than ‘n’, it will return the value as ‘FALSE’.

Compare Numbers with ‘<=’ Operator in Excel

We can also use the ‘Less than or equal to’ operator to compare two different numbers and the operator will return the correct value easily. This operator can also be used to create complex mathematical operations in Excel.

You can use the ‘less than or equal to’ operator with mathematical operators or logical operators to create some complex mathematical operations. below we have an example that you can try entering in Excel.

 =(A4>B3)+(A1*B5)+(B2/2)+(B6<=A3)

For mathematical calculations, the result of the logical operation ‘TRUE’ stands for 1 and ‘FALSE’ stands for 0.

Compare Dates with ‘<=’ Operator in Excel

The use of the logical operator doesn’t stop with numbers and the alphabet. you can also use it to compare the data values. The logical operators can also be used to compare data types, like date and text or number and text.

Do keep in mind, while comparing dates, Microsoft Excel saves the dates and time as numbers. However, they are formatted to look like dates. Also, the Excel date number starts from the 1st of January 1900 at 12:00 AM, this date is saved as 1. Similarly, the 2nd of January is saved as 2 and so on.

Below we have a list of dates entered in Excel.

Now, to see the numbers behind the dates, you need to press the shortcut keys Ctrl+~ from the keyboard. Or you can simply change the format of the date to the number or general and Exel will give you the number value of each date written just like shown in the image.

Microsoft Excel always uses these numbers whenever a date is involved in a calculation.

Below we have discussed the whole date situation with an example.

1. C2: A2 date is less than the B2, hence, TRUE.
2. C3: A3 (which number is 42139) is greater than B3 – FALSE.
3. C4: A4 is less than B4 – TRUE.
4. C5: A5 (36666.263) is greater than B5 (36666). When only a date is entered, its default time is 12:00 AM, which is midnight. So the answer is FALSE
5. C6: A6 is greater than B6. Since a text is always considered as the largest value when you compare it to any number or date in Microsoft Excel. Hence, the value is ‘FALSE’.

Often Excel considers the date value as a text string or arithmetic calculation when you compare a date value with a cell.

Just like in the below example, we see that even though the value of the A1 cell is greater than ‘4-12-2020. But the result is ‘TRUE’. This is because Excel considers the value as a text string.

Not only that but the date part (5-12-20200 from the formula is also considered a mathematical calculation. To fix the issue you will have to enclose a date in the DATEVALUE function like this-.

=A1<=DATEVALUE("5-12-2020")

After using the above formula, you will get the correct result while comparing.

Using ‘less than or Equal to’ Operator with Functions

In Microsoft Excel, logical operators like <= are widely used in the parameters of Excel functions such as IF, SUMIF, COUNTIF and many other operators and functions are used to perform some powerful and complex calculations.

Using ‘<=’ with IF Function in Excel

The ‘<=’ operator can be used with several different functions, here we will try to use it with the IF function. The <= operator can be used with the ‘logic_test’ argument of the IF function to perform logical operations.

The Excel IF function can evaluate a logical condition ( which is made by the ‘less than or equal to’ operator) and then return one value if the condition is TRUE or for any other value if the contrition is FALSE.

The syntax and formula for using the IF function is-

=IF(logical_test,[value_if_true],[value_if_false])

To understand the IF function better along with the ‘less than or equal to’ operator, below we have taken a sample where we have a list of students’ marks and then you want to check whether each student is passed or failed based on the marks they have got. So, to do that, we need the formula written below-

=IF(B2<=50,"Fail","Pass")

let’s say the passing mark is ’50’. So the logical_test argument will be 50. The formula will check if the value in the B2 cell is less than or equal to 50 and then returns ‘Fail’ if the condition is TRUE or returns ‘Pass in case the condition is FALSE.

We will apply the same formula for all of the cells to get the result for each student.

Using ‘<=’ with SUMIF Function in Microsoft Excel

As you all know, several different functions work on Excel, this time we will be using the SUMIF function with the ‘less than or equal to’ operator to solve mathematical problems for a range of different cells. The SUMIF function is usually used to sum a range of cells when corresponding cells match certain criteria.

The syntax of the function SUMIF is-

=SUMIF(range,criteria,[sum_range])

Let’s take an example to understand the function of SUMIF function in a better way. Let’s say you want to sum all the sales that have happened on or before (<=) January 01, 2019, from the below table. You can use the ‘less than or equal to’ operator with the SUMIF function to sum all the relevant values that are required for your problem. The formula will be used as-

=SUMIF(A2:A16,"<=01-Jan-2020",C2:C16)

The above formula will check for all the sales that have occurred on or before (<=) 01 January 2020 from the cell range A2:A16 and then sums all the sales amounts that are corresponding to those matching dates from the cell range between C2:C16.

Using ‘<=’ with COUNTIF Function in Excel

Another function that is commonly used in Microsoft Excel is the COUNTIF function. So, now let us try to use the COUNTIF function along with the ‘less than or equal to’ operator. The COUNTIF Function on Excel is used to count the cells that meet the given criteria from a range of cells.

You can also use the <= operator to count the number of cells with a value that is less than or equal to the specified value by the user. The syntax or the formula to use the COUNTIF function is-

=COUNTIF(range,criteria)

Do note that you have to use the <= operator in the criteria argument of the function and then a range of cells where you want to count the cells in the range argument of the formula.

Below we have an example explaining the use of the COUNTIF function along with less than or equal to operator.

Let us say, you want to count the sales that are less than or equal to 1000. You will have to use the below formula to get the answer.

=COUNTIF(C2:C16,"<=1000")

The above-written formula will count the cells that are either less than or equal to 1000 in the range of cells from C2 to C16 and then will display the result in cell F4.

Besides the IF, SUMIF and COUNTIF functions you can also use the ‘less than or equal to’ operator with other less popular functions such as AND, OR, NOR or XOR.

Using ‘<=’ Operator in Excel Conditional Formatting

You can also use the ‘Less than or equal to’ operator with Excel conditional formatting. The Excel conditional formatting helps users in highlighting or differentiating the data stored in your worksheet based on certain different criteria which can be decided by the user.

For instance, if you want to highlight sales amounts that are less than or equal to 2000 in column C. Then you have to write a simple rule using the less than or equal to the operator in the Excel conditional formatting. Below is the tutorial for the same.

1. First, you need to select the range of cells where you want to apply the rule for conditional formatting.
2. Next, go to the Home tab and then click on the Conditional Formatting and select New Rule from the drop down that appears.
3. Now, inside the New Formatting Rule dialogue box, you have to select the Use a formula to determine which cells to format option that is located under the Select a Rule Type section.
4. Type the below written formula to highlight the sales that are less than or equal to 2000 in the Format values where this formula is a true box.
   • =C2<=2000
     
5. Once you have entered the rule, you can click on the Format button which will specify the formatting.
6. Once you see the Format Cells dialogue box, you need to choose the specific formatting that you want to apply to highlight cells. Next, you can change the number format, font format, border style and fill the color of the cells. After you have chosen the format for everything you can click on OK to confirm your conditional formatting.
7. Now, back to the new Formatting Rule dialogue box, you will be able to see the preview of your selected format. Click on OK again to apply the formatting and then highlight the cells.
8. You will be able to see the sales that are less than or equal to 2000 are highlighted in column C as we have set the formatting for the same.

To Conclude

So, this was an extensive guide and a detailed review on how and where you can use the ‘less than or equal to’ operator. Along with that, we have also mentioned the syntax and formula for different Microsoft Excel functions and how you can use the operators along with those functions.