Definition of Logical Functions

Excel’s logical functions are used to perform analytical tests to reveal whether a statement is true or false. Depending on the outcome of the logical trial, a specific result is returned.

If()

This function returns a value depending on the logical test given as an argument of the function. The syntax of the function is =IF(Logical_test,Result_if_true,Result_if_false)

Logical_test is any value or expression that can be evaluated as true or false. Result_if_true is the value that will return if the Logical_test is true. Result_if_false is the value that will return if the Logical_test is false.

Example 1:

The figure shows a worksheet with student grades in column B. Formulas in column C use the IF function to return text either Pass (a score of 65 or higher) or Fail(a score below 65).

The formula in cell C2 is: =IF(B2>=65,”Pass”,”Fail”)

Example 2: Nested IF

You can “nest” IF functions to provide even more decision-making ability. This formula, for example, returns one of four strings. Excellent (a score of 90 or higher), Very Good (a score of 70 or higher but less than 90), Fair (a score of 50 or higher but less than 70), or Poor (a score less than 50).

In the formula in cell D3, the first logical test will be C3>=90, If this returns true, the cell value should be Excellent, but if it returns false what should be the value: Very Good, Fair, or Poor? So there should be another logical test and another If function that can be nested inside. The logical test should be C3>=70. Again if this test returns false you need to take another decision: whether the value is more than or equal to 50 or less. So one more If the function should be nested. Thus the final formula in cell D3 is: =If(C3>=90,”Excellent”,If(C3>=“Very Good”,If(C3=>50,”Fair”,”Poor”)))

AND() and OR() Function

We often have to work with multiple conditions to come to a decision. Excel provides an efficient way to work with these conditions through these two functions. Both these functions can accept more than one condition (maximum 30) as their arguments and returns a logical value, i.e, TRUE or FALSE. AND() returns true only when all the conditions given are true otherwise returns false. On the other hand OR() returns true when any one of the conditions given is true. It returns false only when all the conditions given are false.

The syntax of AND() is : =AND(condition1,condition2,condition3,……….).

The syntax of OR() is : =OR(condition1,condition2,condition3,……….).

These two functions are widely used in place of logical tests on IF() function as like logical tests these functions also return true/false. Let us explain the function with the following examples:

Example 1:

A builder’s merchant gives a 10% discount on certain product lines. The discount is only given on products which are on Special Offer when the Order Value is Rs,1000 o above.

In this example, we have to check two conditions to offer a discount to the customer, one is whether the product is on Special Offer and another one is whether the Order Value is Rs 1000 or above. That can be checked through AND() function [=AND(B3=“Yes”,C3>=1000)]. Now based on that conditions, we have to take a decision, on whether to offer a discount or not, So this above function has to be incorporated with If() function. Thus the final formula is : =If(AND(B3=“Yes”,C3>=1000),C3810%,0).

Example 2:

A handling charge of Rs 50/- is made on all orders paid by Visa or Delta Cards. The =OR() function has been used to determine whether the charge need to be applied.

In this example, any of the two conditions (whether the payment type is Visa or Delta) has to be satisfied to change the Handling charge of Rs,50. So, Or() function has to be used within the If() function. The final formula is: IF(Or(C3=“Visa”, C3=“Delta”),50,0).

Not()

Not () function is used to reverse the value of its argument. Not() function accept a logical value as its argument. Use Not() function when you want to perform an action if the condition (s) are not met.

Example :

The =NOT() function has been used to calculate whether the book was returned within the correct time, by adding the Loan value to the Taken date. If the book was not returned on time the result Overdue is shown, otherwise OK is shown.

The formula in D2 is

=IF(NOT(C2<=A2+B2).”Overdue”,”OK”)

This formula can also be written without using Not() function:

=if(C2>A2+B2,”Overdue”,”OK”)