Download Using logical operators and functions in excelUsing logical operators and functions in Excel and more Study notes Advanced Computational Complexity in PDF only on Docsity!
Get smarter in just five minutes
Select Language
Home Learn Microsoft Excel Learn Microsoft Word Learn Google Analytics Forums Contact Us
Home
Using logical operators and functions in Excel
Microsoft Excel
A lot of work in Excel involves comparing data in different cells. When you make a comparison between two values, you want to know one of these things:
Is value A equal to value B (A=B) Is A greater than B (A>B) Is A less than B (A<B) Is A greater than or equal to B (A>=B) Is A less than or equal to B (A<=B) Is A not equal to B (A<>B)
These are called logical or boolean operators because there can only be two possible answers in any given case - TRUE or FALSE.
Using Logical Operators in your formulas
118 reddit 9 9 googleplus 18 4 2
Excel is very flexible in the way that these logical operators can be used. For example, you can use them to compare two cells, or compare the results of one or more formulas. For example:
=A1=A =A1=(A25) =(A110)<=(A2/5)
As these examples suggest, you can type these directly into a cell in Excel and have Excel calculate the results of the formula just as it would do with any formula. With these formulas, Excel will always return either TRUE or FALSE as the result in the cell.
A common use of logical operators is found in Excel's IF function (you can read more about the IF function here). The IF function works like this:
=IF(logical_test,value_if_TRUE,value_if_FALSE)
In essence, the IF function carries out a logical test (the three examples above are all logical tests) and then return the appropriate result depending on whether the result of the test is true or false. For example:
=IF(A1>A2,"Greater than","Less than") =IF(A1>A2,A110%,A15%)
However, you don't always need to use an IF formula. Here's a version of this formula that uses a logical operator, and also demonstrates another useful feature of logical operators in general:
=(A1>A2)(A110)+(A1<=A2)(A1*5%)
It looks confusing, but in fact it is very logical (excuse the pun). However, it helps to know that in Excel, TRUE is the same as 1, and FALSE is the same as 0.
So, in this example:
If A1>A2 is TRUE, then the formula will multiple (A110%) by 1. Because A1>A2 is TRUE then A1<=A2 is false, so it will then multiply (A15%) by 0. It will then add the results together: (A110%)1 + (A15%)0. The final result is whatever (A1*10%) equals in the specific example.
Obviously, if A1 is less than A2, then the reverse of this would occur.
Using Multiple Logical Operators
=NOT(Sky is Blue) will return FALSE if the sky is blue, and TRUE if the sky is not blue.
Note that this example doesn't care what other colors the sky might be!
Of course, you can use the NOT function with the AND, OR and IF functions:
=NOT(AND(A1>A2,A1<A3)) - if A1>A2 AND A1<A3, then return FALSE =AND(NOT(A1>A2),A1<A3) - if A1 is NOT >A2 AND A1<A3 then return TRUE.
Note that writing NOT(A1>A2) is another way of writing (A1<=A2). In this simple example, using a NOT function didn't add much value, but in some cases the NOT function can be very handy.
In summary, a lot of what you do in Excel, particularly once you start using IF functions, involves using logical operators. The logical functions, AND, OR and NOT are a great way to extend your use of logical operators to perform more complex calculations.
Want to learn more? Try these lessons:
How to use the IF function in Excel to calculate values based on different criteria Using multiple IF statements in Excel Using SUMIF to add up cells in Excel that meet certain criteria Use SUMIFS to sum cells that match multiple criteria in Excel Use COUNTIF to count the cells in a range that match certain values
Add new comment Search
118 reddit 9 9 googleplus 18 4 2
Similar lessons
How to use the IF function in Excel to calculate values based on different criteria Using multiple IF statements in Excel Using SUMIF to add up cells in Excel that meet certain criteria Use SUMIFS to sum cells that match multiple criteria in Excel Use COUNTIF to count the cells in a range that match certain values
Join our mailing list
Get smarter in just five minutes! Join our mailing list (it's free!) and get updates whenever new lessons are added!
Email Address *
First Name *
Subjects I'm interested in
I want to learn more about:
Like Excel? Try
Klipfolio
Functions. Formulas. All Your
Data. Create Your Dashboard.
Free Trial.
Our Comment Policy.
We welcome your comments and questions about this lesson. We don't welcome spam. Our readers get a lot of value out of the comments and answers on our lessons and spam hurts that experience. Our spam filter is pretty good at stopping bots from posting spam, and our admins are quick to delete spam that does get through. We know that bots don't read messages like this, but there are people out there who manually post spam. I repeat - we delete all spam, and if we see repeated posts from a given IP address, we'll block the IP address. So don't waste your time, or ours.
Add a comment to this lesson
Comments on this lesson
Five Minute Lessons 529
Facebook social plugin
Like
Five Minute Lessons
Have you ever imported data from another system into Excel and found that the times provided in the data convert into text values rather than valid times in Excel? Fortunately, the TIMEVALUE function exists to solve just that situation. It has a simple job - take a text value and convert it into a time value. Of course, there's a catch ... the text has to look like a valid time. More than that - Excel has to recognise it as a valid time. This can be the cause of much hair pul... See More
April 9 at 6:04pm
Excel to mobile app
Use excel to create mobile apps for IOS/Android/Windows
typo in Using logical operators
Submitted by jon Kelly on Mon, 05/05/2014 - 22:
•=(A1>A2)(A110)+(A1<=A2)(A1*5%) It looks confusing, but in fact it is very logical (excuse the pun). However, it helps to know that in Excel, TRUE is the same as 1, and FALSE is the same as 0.
So, in this example:
•If A1>A2 is TRUE, then the formula will multiple (A110%) by 1. •Because A1>A2 is TRUE then A1<=A2 is false, so it will then multiply (A15%) by 0. •It will then add the results together: (A110%)1 + (A15%)0. •The final result is whatever (A1*10%) equals in the specific example. Obviously, if A1 is less than A2, then the reverse of this would occur.
Should read: •=(A1>A2)(A110%)+(A1<=A2)(A15%)
reply
Excel: "If greater then"...works just opposite
Submitted by Tad on Sat, 05/31/2014 - 10:
reply
PT05M
Article Independent Learning
Website development and content by Millionleaves.com
