April 21, 2021

IF function in Excel

In this lesson we will learn how the IF function can be used in Excel. The IF function evaluates a condition and returns respective value based on the result of the evaluation.

=IF(condition, success_return, failure_return)
  • condition – A value or logical expression that is evaluated as TRUE(1) or FALSE(0).
  • success_return – [optional] The value to return when condition evaluates to TRUE(1).
  • failure_return – [optional] The value to return when condition evaluates to FALSE(0).

Let’s understand with a simple example. In the below table, we have used the IF function in cell B1 which is evaluating a condition containing AND function. The value to be returned if the condition evaluates to TRUE is “Approved”. If the condition evaluates to FALSE, the FALSE() function will be triggered.

=IF(AND(A2>0,A2<100),"Approve",FALSE())

In this example, since the condition is not met as the content of cell A2 is 150 and 150>0 but 150 is not less than 100. So it makes the AND function to return 0, which in turn triggers FALSE() function inside IF and results FALSE in cell B2.

In the same example we just changed the price value as 50 in cell A2 and this will make both conditions of the AND function to return true which in turn makes the result of the AND function as 1 and in this case, since the conditions are met, it returned “Approve” as the result in cell B2.

Let’s see another example. In this case if the condition evaluates to TRUE, the TRUE() function will be triggered. The value to be returned if the condition evaluates to FALSE is “Reject”.

=IF(AND(A2>0,A2<100),TRUE(),"Reject")

In this example, since the condition is met as the price value in cell A2 is 50 and 50>0 as well as 50<100. So it makes both arguments of AND function as 1. The AND function returns 1 which in turn triggers TRUE() function inside IF and results TRUE in cell B2.

In the same example we just changed the price value as 150 in cell A2 and this will make one of the AND condition to return false which in turn makes the result of the AND function as 0 and in this case the TRUE() function is not triggered. So as per this IF condition, since the conditions are not met, it returned “Reject” as the result in cell B2.

Let’s see one last example. In the below table, we have used the IF function in cell B1 which is evaluating a condition containing OR function. The value to be returned if the condition evaluates to TRUE is “Approve”. If the condition evaluates to FALSE, it will return “Reject”.  In below table Price has been mentioned as 5 in cell A2 and 55 in cell A3. 

=IF(OR(A2>10,A3<30),"Approve","Reject")
  • The first comparison in OR function returns FALSE as A2=5 and 5 is not greater than 10.
  • The second comparison in OR function also returns FALSE as A3=55 and 55 is not less than 30.

Hence the result of OR function is FALSE which makes the IF function to return “Reject” in cell B2.

In the same table the Price value has been updated as 35 in cell A2 and the OR function in the IF condition has been modified in cell B2 as follows.

=IF(OR(A2>10,A2<30),"Approve","Reject")
  • Here the first comparison in OR function returns TRUE as 35 is greater than 10.
  • The second comparison in OR function returns FALSE as 35 is not less than 30.

Hence the result of OR function is TRUE which makes the IF function to return “Approve” in cell B2.

You can also check here to understand how the IF function works in Google Sheet.

Leave a Reply

Your email address will not be published. Required fields are marked *