Just like the IF function that we learned in our previous article “10-basic-excel-functions-everyone-know”, the AND, OR and NOT functions are logical functions in Excel. These functions are used for logical comparisons in IF function.
1. AND and OR Functions
We can add up to 30 arguments in these functions. Let us discuss some difference between them:
In AND function all arguments we enter in comparison must be true to return a true result. Out to all the arguments, only one comparison will be false to make the result FALSE.
Whereas with an OR function, out of all comparisons, only one of the comparisons will be TRUE to return a result of TRUE.
Syntax for AND Function
=AND(logical1,logical2,……….logical30)
Syntax for OR Function
=OR(logical1,logical2,……….logical30)
Let us understand this difference using an example.
First, we will use the AND function for the below example.
A company needs to compare the sales of salesman and award them with commissions according to some criteria. An employee gets a 10% commission when his sale meets a target of $1,500 AND he must have worked for the company for 2 or more years.
An employee gets a 10% commission when his sale meets a target of $1,500 AND he must have worked for the company for 2 or more years.
In the above example, we need to make 2 comparisons. If these comparisons are fulfilled with any of the salesmen, he gets the 10% bonus.
If the sale of a person is less than $1500 and year of service is less than 2 years, then commissions will be is zero.
Formula applied here is =IF(AND(B2>=1500,C2>=2),B2*10%,”0″)
Let us use OR function with the same example. This will generate a different result
Here we will compare in changing the logic. Earlier we had used AND function, where if both the conditions match then only the employee is eligible for 10% commission.
But using OR function, an employee will get 10% commission if any one of the two conditions provided is fulfilled.
Either he reaches the $1500 target OR he has worked for the company for 2 or more years.
Formula applied here is =IF(OR(B2>=1500,C2>=2),B2*10%,”0″).
Therefore, he would get his 10% commission if even if he has sales of only $1,000 but his tenure is over 2 years as we can see in the above image.
The maximum number of logical comparisons allowed in both the functions AND and OR are 30.
Each logical comparison will be separated with a comma. Please remember that these 30 comparisons are allowed as long as it does not exceed 1,024 characters in total. If it exceeds then less than 30 conditions be allowed.
2. NOT function
The NOT function has only one argument. It can be used on its own also, but it is usually used along with IF function.
Normally the logical functions return TRUE if the matched argument is true and FALSE if the matched argument is false.
But NOT function gives FALSE if the matched argument is true, and gives TRUE if the matched argument is false.
Syntax for NOT function
=NOT(logical)
To understand it better let us use and example.
A school needs to send a letter to the students who failed. For finding this a formula has to be created to put the word “Send” next to those who have failed.
The formula applied to get “Send” as result is =IF(NOT(D21=”Pass”),”Send”,””).
The result will be shown as in the image below
If you have any queries or suggestions please comment below. I will be happy to assist you.
Leave a Reply