by Many tasks you perform in Excel involve comparing data in different cells. For this, Microsoft Excel provides six logical operators, which are also called comparison operators. This tutorial aims to help you understand the insight of Excel logical operators and write the most efficient formulas for your data analysis.
Excel logical operators - overview
A logical operator is used in Excel to compare two values. Logical operators are sometimes called Boolean operators because the result of the comparison in any given case can only be either TRUE or FALSE.
Six logical operators are available in Excel. The following table explains what each of them does and illustrates the theory with formula examples.
| Condition | Operator | Formula Example | Description |
| Equal to | = | =A1=B1 | The formula returns TRUE if a value in cell A1 is equal to the values in cell B1; FALSE otherwise. |
| Not equal to | <> | =A1<>B1 | The formula returns TRUE if a value in cell A1 is not equal to the value in cell B1; FALSE otherwise. |
| Greater than | > | =A1>B1 | The formula returns TRUE if a value in cell A1 is greater than a value in cell B1; otherwise it returns FALSE. |
| Less than | < | =A1<B1 | The formula returns TRUE if a value in cell A1 is less than in cell B1; FALSE otherwise. |
| Greater than or equal to | >= | =A1>=B1 | The formula returns TRUE if a value in cell A1 is greater than or equal to the values in cell B1; FALSE otherwise. |
| Less than or equal to | <= | =A1<=B1 | The formula returns TRUE if a value in cell A1 is less than or equal to the values in cell B1; FALSE otherwise. |
The screenshot below demonstrates the results returned by Equal to, Not equal to, Greater than and Less than logical operators:
It may seem that the above table covers it all and there's nothing more to talk about. But in fact, each logical operator has its own specificities and knowing them can help you harness the real power of Excel formulas.
Using "Equal to" logical operator in Excel
The Equal to logical operator (=) can be used to compare all data types - numbers, dates, text values, Booleans, as well as the results returned by other Excel formulas. For example:
| =A1=B1 | Returns TRUE if the values in cells A1 and B1 are the same, FALSE otherwise. |
| =A1="oranges" | Returns TRUE if cells A1 contain the word "oranges", FALSE otherwise. |
| =A1=TRUE | Returns TRUE if cells A1 contain the Boolean value TRUE, otherwise it returns FALSE. |
| =A1=(B1/2) | Returns TRUE if a number in cell A1 is equal to the quotient of the division of B1 by 2, FALSE otherwise. |
Example 1. Using the "Equal to" operator with dates
You might be surprised to know that the Equal to logical operator cannot compare dates as easily as numbers. For example, if the cells A1 and A2 contain the date "12/1/2014", the formula =A1=A2 will return TRUE exactly as it should.
However, if you try either =A1=12/1/2014 or =A1="12/1/2014" you will get FALSE as the result. A bit unexpected, eh?
The point is that Excel stores dates as numbers beginning with 1-Jan-1900, which is stored as 1. The date 12/1/2014 is stored as 41974. In the above formulas, Microsoft Excel interprets "12/1/2014" as a usual text string, and since "12/1/2014" is not equal to 41974, it returns FALSE.
To get the correct result, you must always wrap a date in the DATEVALUE function, like this =A1=DATEVALUE("12/1/2014")
Note. The DATEVALUE function needs to be used with other logical operator as well, as demonstrated in the examples that follow.
The same approach should be applied when you use Excel's equal to operator in the logical test of the IF function. You can find more info as well as a few formula examples in this tutorial: Using Excel IF function with dates.
Example 2. Using the "Equal to" operator with text values
Using Excel's Equal to operator with text values does not require any extra twists. The only thing you should keep in mind is that the Equal to logical operator in Excel is case-insensitive, meaning that case differences are ignored when comparing text values.
For example, if cell A1 contains the word "oranges" and cell B1 contains "Oranges", the formula =A1=B1 will return TRUE.
If you want to compare text values taking in to account their case differences, you should use the EXACT function instead of the Equal to operator. The syntax of the EXACT function is as simple as:
Where text 1 and text2 are the values you want to compare. If the values are exactly the same, including case, Excel returns TRUE; otherwise, it returns FALSE. You can also use the EXACT function in IF formulas when you need a case-sensitive comparison of text values, as shown in the below screenshot:
Note. If you want to compare the length of two text values, you can use the LEN function instead, for example =LEN(A2)=LEN(B2) or =LEN(A2)>=LEN(B2).
Example 3. Comparing Boolean values and numbers
There is a widespread opinion that in Microsoft Excel the Boolean value of TRUE always equates to 1 and FALSE to 0. However, this is only partially true, and the key word here is "always" or more precisely "not always" : )
When writing an 'equal to' logical expression that compares a Boolean value and a number, you need to specifically point out for Excel that a non-numeric Boolean value should be treated as a number. You can do this by adding the double minus sign in front of a Boolean value or a cell reference, e. g. =A2=--TRUE or =A2=--B2.
The 1st minus sign, which is technically called the unary operator, coerces TRUE/FALSE to -1/0, respectively, and the second unary negates the values turning them into +1 and 0. This will probably be easier to understand looking at the following screenshot:
Note. You should add the double unary operator before a Boolean when using other logical operators such as not equal to, greater than or less than to correctly compare a numeric and Boolean values.
When using logical operators in complex formulas, you might also need to add the double unary before each logical expression that returns TRUE or FALSE as the result. Here's an example of such a formula: SUMPRODUCT and SUMIFS in Excel.
Using "Not equal to" logical operator in Excel
You use Excel's Not equal to operator (<>) when you want to make sure that a cell's value is not equal to a specified value. The use of the Not equal to operator is very similar to the use of Equal to that we discussed a moment ago.
The results returned by the Not equal to operator are analogous to the results produced by the Excel NOT function that reverses the value of its argument. The following table provides a few formula examples.
| Not equal to operator | NOT function | Description |
| =A1<>B1 | =NOT(A1=B1) | Returns TRUE if the values in cells A1 and B1 are not the same, FALSE otherwise. |
| =A1<>"oranges" | =NOT(A1="oranges") | Returns TRUE if cell A1 contains any value other than "oranges", FALSE if it contains "oranges" or "ORANGES" or "Oranges", etc. |
| =A1<>TRUE | =NOT(A1=TRUE) | Returns TRUE if cell A1 contains any value other than TRUE, FALSE otherwise. |
| =A1<>(B1/2) | =NOT(A1=B1/2) | Returns TRUE if a number in cell A1 is not equal to the quotient of the division of B1 by 2, FALSE otherwise. |
| =A1<>DATEVALUE("12/1/2014") | =NOT(A1=DATEVALUE("12/1/2014")) | Returns TRUE if A1 contains any value other than the date of 1-Dec-2014, regardless of the date format, FALSE otherwise. |
Greater than, less than, greater than or equal to, less than or equal to
You use these logical operators in Excel to check how one number compares to another. Microsoft Excel provides 4 comparison operates whose names are self-explanatory:
- Greater than (>)
- Greater than or equal to (>=)
- Less than (<)
- Less than or equal to (<=)
Most often, Excel comparison operators are used with numbers, date and time values. For example:
| =A1>20 | Returns TRUE if a number in cell A1 is greater than 20, FALSE otherwise. |
| =A1>=(B1/2) | Returns TRUE if a number in cell A1 is greater than or equal to the quotient of the division of B1 by 2, FALSE otherwise. |
| =A1<DATEVALUE("12/1/2014") | Returns TRUE if a date in cell A1 is less than 1-Dec-2014, FALSE otherwise. |
| =A1<=SUM(B1:D1) | Returns TRUE if a number in cell A1 is less than or equal to the sum of values in cells B1:D1, FALSE otherwise. |
Using Excel comparison operators with text values
In theory, you can also use the greater than, greater than or equal to operators as well as their less than counterparts with text values. For example, if cell A1 contains "apples" and B1 contains "bananas", guess what the formula =A1>B1 will return? Congratulations to those who've staked on FALSE : )
When comparing text values, Microsoft Excel ignores their case and compares the values symbol by symbol, "a" being considered the lowest text value and "z" - the highest text value.
So, when comparing the values of "apples" (A1) and "bananas" (B1), Excel starts with their first letters "a" and "b", respectively, and since "b" is greater than "a", the formula =A1>B1 returns FALSE.
If the first letters are the same, then the 2nd letters are compared, if they happen to be identical too, then Excel gets to the 3rd, 4th letters and so on. For example, if A1 contained "apples" and B1 contained "agave", the formula =A1>B1 would return TRUE because "p" is greater than "g".
At first sight, the use of comparison operators with text values seems to have very little practical sense, but you never know what you might need in the future, so probably this knowledge will prove helpful to someone.
Common uses of logical operators in Excel
In real work, Excel logical operators are rarely used on their own. Agree, the Boolean values TRUE and FALSE they return, though very true (excuse the pun), are not very meaningful. To get more sensible results, you can use logical operators as part of Excel functions or conditional formatting rules, as demonstrated in the below examples.
1. Using logical operators in arguments of Excel functions
When it comes to logical operators, Excel is very permissive and allows using them in parameters of many functions. One of the most common uses is found in Excel IF function where the comparison operators can help to construct a logical test, and the IF formula will return an appropriate result depending on whether the test evaluates to TRUE or FALSE. For example:
=IF(A1>=B1, "OK", "Not OK")
This simple IF formula returns OK if a value in cell A1 is greater than or equal to a value in cell B1, "Not OK" otherwise.
And here's another example:
=IF(A1<>B1, SUM(A1:C1), "")
The formula compares the values in cells A1 and B1, and if A1 is not equal to B1, the sum of values in cells A1:C1 is returned, an empty string otherwise.
Excel logical operators are also widely used in special IF functions such as SUMIF, COUNTIF, AVERAGEIF and their plural counterparts that return a result based on a certain condition or multiple conditions.
You can find a wealth of formula examples in the following tutorials:
2. Using Excel logical operators in mathematical calculations
Of course, Excel functions are very powerful, but you don't always have to use them to achieve the desired result. For example, the results returned by the following two formulas are identical:
IF function: =IF(B2>C2, B2*10, B2*5)
Formula with logical operators: =(B2>C2)*(B2*10)+(B2<=C2)*(B2*5)
I guess the IF formula is easier to interpret, right? It tells Excel to multiply a value in cell B2 by 10 if B2 is greater than C2, otherwise the value in B1 is multiplied by 5.
Now, let's analyze what the 2nd formula with the greater than and less than or equal to logical operators does. It helps to know that in mathematical calculations Excel does equate the Boolean value TRUE to 1, and FALSE to 0. Keeping this in mind, let's see what each of the logical expressions actually returns.
If a value in cell B2 is greater than a value in C2, then the expression B2>C2 is TRUE, and consequently equal to 1. On the other hand, B2<=C2 is FALSE and equal to 0. So, given that B2>C2, our formula undergoes the following transformation:
Since any number multiplied by zero gives zero, we can cast away the second part of the formula after the plus sign. And because any number multiplied by 1 is that number, our complex formula turns into a simple =B2*10 that returns the product of multiplying B2 by 10, which is exactly what the above IF formula does : )
Obviously, if a value in cell B2 is less than in C2, then the expression B2>C2 evaluates to FALSE (0) and B2<=C2 to TRUE (1), meaning that the reverse of the described above will occur.
3. Logical operators in Excel conditional formatting
Another common use of logical operators is found in Excel Conditional Formatting that lets you quickly highlight the most important information in a spreadsheet.
For example, the following simple rules highlight selected cells or entire rows in your worksheet depending on a value in column A:
Less than (orange): =A1<5
Greater than (green): =A1>20
For the detailed-step-by-step instructions and rule examples, please see the following articles:
As you see, the use of logical operators in Excel is intuitive and easy. In the next article, we are going to learn the nuts and bolts of Excel logical functions that allow performing more than one comparison in a formula. Please stay tuned and thank you for reading!
1256 comments
Dear Please tell me the formula for the bellow condition
I am trying to set up a result based on the score achieved, example if some one got <80 should study Starter book and if some one got < 105 should study book 1 and if some one got <120 should study book 2 and so one tell book 5, please help me in this regard. ASAP.
Hi Mohammad,
You can use nested IF functions like this:
=IF(A1<80, "starter", IF(A1<105, "book 1", IF(A1<210, "book 2", "")))
Where A1 is the score.
Hi,
Please help me to find the solution
A2 = Day or Week or DayWeek
B2 = Arive date
C2 = Depart date
E2 = The number of days
Thsi is a hotel and guest stays the charges are days and weeks, lets say a guest stays for 7 days the then the charges should be calculated for 01 week and balance days, so how to calculate this if A2=Day the answer total days, if A2=DayWeek the answer the total days but should be reduced by 07 days (01 week)
Eg
A2 => Day or Week or DayWeek
B2 => 01/04/2015
C2 => 10/04/2015
E2 => if A2 is day the 10 days
E2 => if A2 is Week then 01 week
E2 => if A2 is DayWeek then 03 days (total days - 01 week)
I want to know the formula to calculate if A2 is DayWeek then the answer should be remain days which reduced the week from total days (10-7=03days)
Hi need help with a formula.
I'm trying to figure out how to make this happen and hope I can explain correctly...I want the formula to look at several cells and say if this cell has 0.00 then look at the cell before it, and if that cell also has 0.00 then look at the cell before it, and so on for several cells. However if any of them are greater than 0.00 then input that first cell amount and don't look at any further cells.
Hi Janet,
What you need is nested IF functions similar to this:
=IF(A1<>0,A1,IF(A2<>0,A2,IF(A3<>0,A3,IF(A4<>0,A4,""))))
Hello,
I'm working on an inventory and order guide for a restaurant. It's set up so it totals the amount of cases ordered for the week and I'd like to set a condition that if we order 16 cases or more we pay $10.00 per case and if we order less than 16 we pay $13.00 per case. It sure would be convenient if I could have this automatically calculated.. :)
Any ideas? Thank you!
Hi Tito,
You can do this using an IF formula. For example:
=IF(A1>=16, A1*10, A1*13)
Where A1 is the amount of cases.
I am trying to have date cell turn colors when over 30 days unless next cell has date in it?
Eg, I got a starting time at 9am and an ending time at 11am and would like to condition that if the number of hours is less than 4 hrs, it will give me 4hr and if more than 4 hours, it should give me 8 hrs. Can anyone help? greatly appreciate any help as I tried all means and unable to solve this. A thousand thanks
I am trying to create the following condition:
If there are any Adjust Hours Listed, then the
Adjust Pay = SM pay - the calculated EE Med LOA pay - MAT LOA pay. If there are no Adjust Hrs listed, than the Adjust Pay cell will be blank.
SM Pay Adj Hrs EE Med LOA Pay MAT LOA Pay Adjust Pay
$4,1791.17 46.67 0.00 1,928.85 2,250.32
Is there a formula for this?
Thank you.
Hi, here is the thing, i have a list of items and i build them, but i need to know how many of those items are part of the requisition and how many are not, lets say i have to build 250 cars at $10 each i aready have built 240, then i build 10.... there im ok but if instead i build 25 i have to know in one cell how many of those are within my requsition (10) and how many are out of budget (15)
thxs.
Currently we have a formula of =IF(P584-P586<100;"RIGHT";"WRONG"). The idea of this formula is to find the difference between the field that has a sum in and the other field which is the posting in our accounting system. But the difference is okay as long as it is no more than 100.
This formula covers one angel of the working but I would also need to do IF(P586-P584<100;"RIGHT";"WRONG") as well to ensure the differences are located from both angels.
How would I be able to combine those two formulas for it to work?
Hi,
I have a query regarding a formula.
I want to have the following but I don't know how to put that into an excel formula:
If b20 = greater or equal to g31 I want it to say accept.
I hope someone can help me with this!
Thank you!!
Hi William,
Try the following IF formula:
=IF(B20>=G31, "accept", "")
Hi Svetlana,
Thank you for your reply, but unfortunatly it is not working..
Do you maybe have another idea?
Thank you!
William,
The formula is so simple that it simply cannot not work :)
Please have a look at this example (the formula is in A1). If you want something different, please clarify.
Great, I got it!
Thank you very much!
If cell A is less than or equal to cell B and less than "2" than its true. Example:
Time to Close (Days) SLA Business Days Met SLA
1.3 1 FALSE
If time to close is less than 2 days I want to "Met SLA" to be True.
I know this much of the formula to get my true or false statement (=I2>=H2) but can't find how to add the less than 2 without creating another column.
Hi Ash,
What you need is the AND statement like this:
=AND(I2>=H2, H2<2)
OMG thank you so much!!! I was about to pull all my hair out!! LOL You're awesome!!
Hi Svetlana,
Would you be able to help out here? The beginning of the formula works, the rest does not.
=IF(D4>=2.51,3,IF(2.51>D4>=2.01,2.5,IF(2.01>D4>=1.51,2,IF(1.51>D4>=1.01,1.5,IF(1.01>D4>=0.51,1,0)))))
The logic behind it is as follows:
If value in cell D4 is bigger than 2.51 round to 3
If the value is between 2.01 - 2.51 round to 2.5 and so on
Many thanks in advance for your help!
Hi Kasia,
You don't want expressions such as 2.51>D4 and the like :)
=IF(D4>=2.51,3,IF(D4>=2.01,2.5,IF(D4>=1.51,2,IF(D4>=1.01,1.5,IF(D4>=0.51,1,0)))))
please can tell me the Sum formula of category wise Die. 1 to 40 is Window and 41 to 43 is Door then Die .44 is Window how can i apply logical formula to identify Door and window by entering Die Number??
Sorry, I do not fully understand the task.
As it's currently written, it's hard to tell exactly what you're asking.
I need a formula to do the following in cell J6
If Cell J46*0.03 is greater than J26 then J6 is = to J26 if not it is equal to J46*0.03
Hi Stephanie,
Here's a formula for cell J6:
=IF(J46*0.03>J26, J26, J46*0.03)
I need a formula to do the following:
if cell A1 is >0 then return value in cell A6 but if cell A1 and cell B1 are >0 then return value in B6 but if cell A1, B1 and C1 are >0 then return value in C6...
Thanks!
Hi Claude,
You need a nested IF formula like this:
=IF(AND(A1>0, B1>0, C1>0), C6, IF(AND(A1>0, B1>0), B6, IF(A1>0, A6, "")))
Hi dear,
I am using a formula =IF(AS$6>=$C135,IF(AS$6<=$D135,($DE135),(0))) to represent the data if in between two dates, if this condition not true show 0....this formula working right. But the issue is that when AS$6<$C135...this shows falls rather than 0....please some 1 suggest me a best possible way
Can you please help me write the a formula to do the following?
If A is =30 but =60 but =90: Return "SR"
Disregard, my text didn't display correctly, but I figured out the formula anyway.
Thanks
I want to be select if less the 7500 then select 175 and above 7500 then select 200 how to use the formula use
Hi Pankaj,
It may be as simple as =IF(A1>7500, 175, 200)
Hi,
Is it possible to use two function in same sell like =MIN(IF(A1:A5=0, "", A1:A5)) and =Round(A1,0) ?
Nope. Only one formula per cell is allowed.
Iam trying to come up with a formula to fibure materials for a project.
The project is round columns Our material is 62" wide so and diamater of the material over 29" mean I have to figure the full width of the sheets
I am trying to have a column for dia of column example
24" column x 3.146 /2 = 37.752 since this is greater than 29 the formula should read 62" I have used this and made work for greater but it did not work when I had a 18" column that would have been less than 29
=IF(A2=29,"OK",A1)
Does this make sense what am I doing wrong?
Hi Gordon,
I am not sure if the cell references are correct because I don't know your data structure. The following formula returns "OK" if the value in A2 is equal to or less than 29, the value from A1 otherwise. Is this what you are looking for?
=IF(A2>=29,"OK",A1)
Amazing - thank you so much!