How to use Logical AND, OR and IF Functions in Excel

How to use Logical AND, OR and IF Functions in Excel

  • Post author:
  • Post category:Formula

The IF function is used to perform logical test and return one value if the condition is TRUE and another for FALSE results. Combining AND, OR and IF function varies the condition for testing the cells.

The OR and AND functions are used to test multiple conditions and depending on which function is used, one or all of the conditions must be true for the function to return a true response.

This tutorial explains how to use the IF function, combination of IF and AND, and If and OR functions.

IF Function

Syntax

= IF(Logical_test,[Value_if_true], [Value_if _false])

Arguments

  1. Logical_test: Is the logical expression or condition that can be evaluated as TRUE or False.
  2. Value_if_true: The value returned when the Logical_test evaluates to TRUE.
  3. Value_if_false: the value returned when Logical_test evaluates to False.

In the example below we will give a discount to employees based on their monthly pay. We will check the value of monthly pay (on cell E3 through to cell E11) if it’s greater than or equal 4500 and return 8%discount. If the value in Cell E3 is less than 4500, return Zero.

Enter the formula =IF(E3>=4500,8%*E3,0) into the cell E3 and press enter. Drag the fill handle to copy the formula to the other cells.



How to use IF function and AND function

The combined IF and AND function is used to check whether a cell meets several conditions at the same time. The logical function tests whether all the conditions are TRUE.

Syntax

= IF(AND(Logical 1, Logical 2, ..), [value_if_true], [value_if_false]))

This function is used to check if a function meets several conditions at the same time. This is done by combining the If function and AND function.

Example: check the risk involved in the different projects. If the project cost is more than 2000 and less than 4600, then the risk is very high otherwise a low risk is experience in the business.

Enter the formula =IF(AND(D2>3000,D2<4600), “HIGH”,”LOW”) into cell E2 and press enter.


Drag the fill handle to copy the formula to cells E2:E10.


How to use IF Function and OR function

In OR function, it checks if one condition is true and returns a value of TRUE. Returns FALSE only if all the arguments are FALSE.

Syntax

=IF(OR(Logical 1, Logical 2, …), [Value_if_true], [Value_if_false])


Click OK


=IF(OR(E2>4000,D2=”Medium”), “True”, “False”).    IF E2 is greater than 4000 OR D2 is equivalent to Medium, return TRUE, otherwise return FALSE. In this case the first argument is true, but the second is false. Since OR only needs one of the arguments to be true, the formula returns TRUE.