1

Using logical functions in Excel: AND, OR, XOR and NOT

The tutorial explains the essence of Excel logical functions AND, OR, XOR and NOT and provides formula examples that demonstrate their common and inventive uses.

If you are interested to learn the Top 3 Functions in excel please refer to the LINK

AND function (Logical Function in excel)

Returns TRUE if all of its arguments are TRUE

Use the AND function, to determine if all conditions in a test are TRUE. Please refer below example

AND Logical function in Excel

The AND function returns TRUE if all its arguments evaluate to TRUE, and returns FALSE if one or more statements evaluate to FALSE.

One common use for the AND function is to expand the usefulness of other functions that perform logical tests. For example, the IF function performs a logical test and then returns one value if the test evaluates to TRUE and another value if the test evaluates to FALSE. By using the AND function as the logical_test argument of the IF function, you can test many different conditions instead of just one.

AND Function Syntax

AND(logical1, [logical2], …)

The AND function syntax has the following arguments:

Logical1

Required. The first condition that you want to test can evaluate as either TRUE or FALSE.

Logical2,

Optional. Additional conditions that you want to test can evaluate as either TRUE or FALSE, up to a maximum of 255 conditions.

P.S:-

  1. The arguments must evaluate logical values, such as TRUE or FALSE, or the arguments must be arrays or references that contain logical values.
  2. Those values are ignored if an array or reference argument contains text or empty cells.
  3. If the specified range contains no logical values, the AND function returns the #VALUE! error.

IF Function

Specifies a logical test to perform

The IF function is one of the most popular functions in Excel, and it allows you to make logical comparisons between a value and what you expect.

So an IF statement can have two results. The first result is if your comparison is True, and the second is if your comparison is False.

For example, =IF(C2=”Yes”,1,2) says IF(C2 = Yes, then return a 1, otherwise return a 2).

IF Function Syntax

Use the IF function, to return one value if a condition is true and another value if it’s false.

IF(logical_test, value_if_true, [value_if_false])

logical_test    (required)

The condition you want to test.

value_if_true    (required)

The value that you want to be returned if the result of the logical_test is TRUE.

value_if_false    (optional)

The value that you want to be returned if the result of logical_test is FALSE.

IF Function Examples

IF function in EXCEL

=IF(C2>B2,” Over Budget”,” Within Budget”)

In the above example, the IF function in D2 is saying IF(C2 Is Greater Than B2, then return “Over Budget”, otherwise return “Within Budget”)

How to use AND and IF function together

Here are some general examples of using AND by itself, and in conjunction with the IF function.

AND

=AND(A2>1,A2<100)

It Displays TRUE if A2 is greater than 1 AND less than 100, otherwise, it displays FALSE.

OR Function (Logical Function)

OR logical test in excel will result if anyone’s logic or condition is satisfied. If both conditions are false, then the results will be false.

 IF function performs a logical test and then returns one value if the test evaluates to TRUE and another value if the test evaluates to FALSE. By using the OR function as the logical_test argument of the IF function, you can test many different conditions instead of just one.

Syntax

OR(logical1, [logical2], …)

The OR function syntax has the following arguments:

Logical1 Required. The first condition you want to test can be evaluated as either TRUE or FALSE.

Logical2 Optional. Additional conditions you want to test can be evaluated to either TRUE or FALSE, up to a maximum of 255 conditions.

Note:- You can use an OR array formula to see if a value occurs in an array. To enter an array formula, press CTRL+SHIFT+ENTER.

Example:-

OR Function in Excel

=OR(A2>1, A2<100)

Displays TRUE if A2 is greater than 1 OR less than 100, otherwise it displays FALSE.

=IF(OR(A2>1,A2<100),A3,”The value is out of range”)

Displays the value in cell A3 if it is greater than 1 OR less than 100, otherwise, it displays the message “The value is out of range”.

=IF(OR(A2<0,A2>50),A2,”The value is out of range”)

Displays the value in cell A2 if it’s less than 0 OR greater than 50, otherwise, it displays a message.

How to use OR and IF function together

Here is a fairly common scenario where we need to calculate if salespeople qualify for a commission using IF and OR.

=IF(OR(B14>=$B$4,C14>=$B$5),B14*$B$6,0) – IF Total Sales are greater than or equal to (>=) the Sales Goal, OR Accounts are greater than or equal to (>=) the Account Goal, then multiply Total Sales by the Commission %, otherwise return 0.

Note:- Useful link

You May Also Like

By admin

3 thoughts on “Best Logical Functions In Excel 2010-2021”
  1. It was specially registered at a forum to tell to you thanks for the help in this question how I can thank you?

Leave a Reply

Your email address will not be published.