How to Use Less Than or Equal to Operator in Excel

“Less Than or Equal to’ operator (<=) is one of the six logical operators (also called comparison operators) employed within Microsoft Excel to compare values. This "<=" operator determines whether the first value is less and equal to another value , and returns TRUE if the answer is yes, or it returns 'FALSE'. It is a boolean operator and therefore it will give either FALSE or TRUE.

The expression ‘less than or equal to’ function is utilized to perform various logic operations that are available in Excel. It is seldom used by itself and is usually used in conjunction along with the other Excel functions like IF OR, NOT, COUNTIF, SUMIF, etc. to make complex calculations. This tutorial will learn how to utilize the ‘less than ‘ or equal to’ (<=)' operator using dates, text and numbers and also using Excel functions.

Compare Text Values using ‘<=' the Operator within Excel

The ‘less than ‘ or ‘equal to’ operator may be used to evaluate text in Excel. Before you can compare in text using Excel it is important to know that all logic operators are case-insensitive. This means that they do not consider the differences in case when making comparisons between text values.

Another thing you must be aware of in comparing string names to logic operators in Excel. MS Excel considers the first alphabet “a” as the smallest value, and the final alphabet “z” as the largest value. This means that a D, r V, k > J and so on. Let us go over an illustration.

Example 1: If you want to verify that the text within cell A3 that is lower than or equal to the value of cell B4, you can use this formula that is easy to follow:

=A3<=B3

Excel formulas must always begin with an equal sign. '='. First argument: the cell A3. The next argument comes from cell B3, while the operator placed between. Because both values are the identical, the result is 'true'.

In lieu of cell reference, you can also utilize direct text values as arguments in formulas. However, when text is used into a formula, it must be enclosed with double quotation marks such as this:

="Ant"<="ant"

Since logic operators are case-insensitive it disregards any differences in case and results in TRUE.

Example 2:

In the following example, "Ant" text is certainly not the same as "Elephant". You might be asking what the difference is, and how Ant is different from Elephant? Could it be because it's smaller? The initial letter of the Cell A3 ("A") is larger that the initial letter in cell B3 ("E").

As we have mentioned earlier, Excel considers that letters that are later within the alphabet may be bigger than letters that are earlier. This formula examines the first letter of A3 with the first letter of A3 with the B3's first letter. The letter A is the first is the first letter of "E", so the formula will return "TRUE".

Example 3:

When comparing texts Excel begins by comparing the initial letter of the text. If they're identical then it moves up to the next letter. In this case, A3's first letter and B3's A3 and B3 is identical, and the formula shifts into the 2nd letter in A3 as well as B3. Then, "p" is not lower that "n", hence, it results in "FALSE".

Compare numbers with '<=' Operation in Excel

Utilizing 'less than' and equal to' in numbers is a simple enough task that anyone can master it. It is also possible to use this function to create complicated mathematical functions using Excel.

Here's an example of how to look at numbers against '<=':

You can apply the "less than or equal" operator in conjunction with mathematical operators and other logical operators to make complicated mathematical operations.

For instance, you can try this formula:

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

In math, the outcome of the logical operation "TRUE" is the same as 1 FALSE, and 1.

This means that the that the first formula (A4>B3) returns "0" and the final portion of formula (B6<=A3) returns 1. Our formula will look like this:

=0+(A1*B5)+(B2/2)+1

The result for the return is "203".

Compare Dates using '<=' the Operator within Excel

In addition to numbers and text In addition to text and numbers, you can make use of the 'less than' and equal to' operators to evaluate dates. Logical operators can be used to evaluate different types of data, such as text and date, or numbers and text, etc.

The first thing to remember when looking at the dates you are comparing is that Excel stores dates and times in numbers, however they are formatted to appear like dates. Excel date numbers begin from 1 ¹ in January, 1900. at 12:00 AM. It will be saved with the number 1. 2. ^2nd in January 1900, which is stored as 2, and the list goes on.

For example, here is a list of dates that have been entered into Excel.

To view the numbers that are behind dates, use one of the keys shortcuts Ctrl + ~ on your keyboard. You can also alter your date's format from number to general. You will be able to see the numbers for the dates above that are entered into Excel as illustrated below.

Excel utilizes these numbers every time the use of dates is part of calculations.

Let's glance at the following table.

• C2: The date A2 is lower than B2, therefore it is TRUE.
• C3: C3 (which numbers 42139) is higher than B3 - false.
• C4 A4 is smaller than B4 True.
• C5:A5 (36666.263) is higher than the B5 (36666). If only a date is entered, the default time is 12:00 AM which is midnight. Therefore, the answer is a resounding NO.
• C6: C6 is more than A6. Since a text is considered to be the most significant value when it is compared to any other number or day in Excel. Hence, it's FALSE.

When you compare the value of a date against a cell, Excel might interpret that date's value text or an arithmetic calculation.

In the following example Even when A1 is higher that "4-12-2020", the result is "TRUE". Because Excel treats the value as an unstructured text string.

Additionally, the date portion (5-12-2020) within the formula can be regarded as an mathematical calculation

To correct this issue problem, you must include an appropriate date within your DATEVALUE function, such as this:

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

You will have the right result:

Utilizing the 'Less Than' or Equal To' Operator in conjunction with Functions

In Excel, logic operators (like <=) are frequently employed in the formulas of Excel functions like IF, SUMIF, COUNTIF and many more functions that perform complex calculations.

Utilizing '<=' the IF Functions Excel. Excel

This '<=' operator is employed in the argument 'logic_test' in the function IF to carry out logic-based operations.

The Excel IF function analyzes the logical conditions (which is created by the "less than or equal to' operator) and returns one value when it is true, or a different value if the condition is false.

The syntax of an IF function would be:

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

Let's say you have a list of students' mark lists and you'd like to determine whether every student has either passed or failed, by their test scores. To check this, use this formula:

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

The mark for passing is '50', which is utilized in the logic_test argument. The formula determines whether the value of B2 is less than or equal to '50' and then returns 'Fail' if the condition is TRUE , or returns 'Pass' in the event that the condition is false.

The same formula is applied to all the other cells.

Another example:

Let's take an example. we have a clothing order list that includes prices. If the cost of the dress is less $150, or is equal to it then we must add a delivery fee of $20 to the price net or add a delivery fee of $10 to the cost. Use this formula to determine that:

=IF(B2<=150, B2+$D$2, B2+$D$3)

If B2's value has less or more than 150, the value of D2 will be added to the value in B2, which is shown in C2. In the event that this condition turns out to be false the D3 value will be added to the B2. We have added the "$" sign between the column numbers as well as row number of cells D2 as well as D3 ($D$2 $D$3) to create absolute cells, which means that it does not change in the event of copying this formula into other cell (C3:C8).

Utilizing '<=' the the SUMIF function in Excel

A different Excel feature that is more frequently employed in conjunction with using the SUMIF function. The SUMIF function can be used to add a number of cells when the cells meet a particular situation.

The basic structure for the SUMIF functions is as follows:

=SUMIF(range,criteria,[sum_range])

Let's take an example. If you're looking to summarize all sales that occurred prior to or on (<=) January 1 January 1, 2019, in the table below You can use SUMIF '<=' operator using the SUMIF function to add all the values:

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

The formula check searches at all sales that took place prior to or on (<=) 01 Jan 2020 in the range of cells A2:A16. It then adds up all sales that correspond to the dates within the range C2:C16.

Utilizing '<=' the COUNTIF Function of Excel

Let's now make use of an logical expression 'less than and equal to' using the function COUONTIF. Excel COUNTIF function can be used to count the cells that satisfy a specific requirement in a set of. It is possible to use this '<=' function to determine how many cells that have an amount that is less than or equivalent to the value specified.

The Syntax of COUNTIF:

=COUNTIF(range,criteria)

You must create a condition using the '<=' operator within the argument for criteria of this function as well as your range of cells in which you will count the cells within the argument for range.

If you are looking to count sales that are less or greater than 1000 in the example below You can apply this formula:

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

The formula above counts cells with a value less than 1,000 or greater than within C2 to C2 up to C16 as well as displays the results in cell F4.

It is also possible to count cells by comparing a criteria value within a cell to the range of cells. In these instances, you write the criteria using the join operator (<=) with an identifier to the cell that contains the value. To accomplish this it is necessary to wrap the operator of comparison in two quotes (""), after which add an ampersand (and) symbol between the logic operator (<=) and the cell's reference.

=COUNTIF(C2:C16,"<="&F3)

In addition to IF, SUMIF and COUNTIF functions, you can also can use the 'less and equal' operators in conjunction with other functions that are not as frequently used, like AND, OR, NOR, or XOR and more.

Using '<=' Operator in Excel Conditional Formatting

Another popular use of the 'less than ' or 'equal to' operator can be found used in Excel Conditional Formatting, which lets to highlight or distinguish the data within your Excel worksheet by a condition.

For instance, if you would like to highlight sales of lower than 2000' within column C you need create a simple formula by using Excel Conditional Formatting. '<=' operator within Excel Conditional Formatting. Here's how to do it:

Then, choose the range of cells in which you would like to apply the rules (condition) then highlight the data (In the case of C2:C16).

Go to the 'Home tab, then click on "Conditional Formatting" and choose "New Rule" from the drop-down menu.

Within the New Formatting Rule dialog box choose the 'Use a formula to determine the cells should be formatted' choice in the Choose the Rule Section. Enter the following formula for highlighting sales that are less than 2000 or equivalent to 2000 into the 'Format values when this formula is true' box:

=C2<=2000

Once you have entered the rule After entering the rule, click the Format button to define the format.

Within the Format Cells dialog box, you have the option of selecting the particular formatting you wish to use for highlighting cells. You can alter the format of the numbers, font border style, format and even the cell's color. After you've selected the format you want, select 'OK'.

In the New Formatting Rule dialog, you will see the preview of the selected format. After that, click OK once more to set the format. You can also mark the cell.

You can observe, sales less or equivalent to 2000 are highlighted in column C.

As you've learned that using the '<=' operation is quite simple and can be used in Excel to make calculations.

That's it.