Learn the IF function in Excel to perform conditional logic in your formulas. Learn syntax, examples, and tips for decision-making in your spreadsheets.
Syntax:
Use the IF function, one of the logical functions, to return one value if a condition is true and another value if it’s false. [^1]
IF(logical_test, value_if_true, [value_if_false])
For example:
=IF(A2>B2,"Over Budget","OK")
=IF(A2=B2,B4-A4,"")
Formula that uses the IF function
logical_test (required): The condition you want to test.
value_if_true (required): The value that you want returned if the result of logical_test is TRUE.
value_if_false (optional): The value that you want returned if the result of logical_test is FALSE.
For example, =IF(C2=”Yes”,1,2) says IF(C2 = Yes, then return a 1, otherwise return a 2).
Example:
=IF(A1>10,"Greater than 10","Less than or equal to 10")
This formula checks if the value in cell A1 is greater than 10. If it is, the formula returns the text “Greater than 10”. If it’s not, the formula returns “Less than or equal to 10”.
Example:
=IF(B1="Yes","Paid","Not Paid")
This formula checks if the value in cell B1 is “Yes”. If it is, the formula returns “Paid”. If it’s not, the formula returns “Not Paid”.
Example:
=IF(C1>=100,"Pass","Fail")
This formula checks if the value in cell C1 is greater than or equal to 100. If it is, the formula returns “Pass”. If it’s not, the formula returns “Fail”.
Example:
the following formula will return “Over Budget” if the value in cell A2 is greater than the value in cell B2, otherwise it will return “OK”:
=IF(A2>B2,"Over Budget","OK")
Example:
the following formula will calculate the difference between the values in cell A2 and cell B2 if the value in cell A2 is greater than the value in cell B2, otherwise it will return 0:
=IF(A2>B2,A2-B2,0)
Here are some more examples of how to use the IF function:
Check if a cell is empty:
=IF(A2="","Empty","Not empty")
Check if a cell contains a specific value:
=IF(A2="Apple","Apple found","Apple not found")
Check if a date is before or after a certain date:
=IF(A2<B2,"Date is before", "Date is after")
Calculate a discount based on the amount of a purchase:
=IF(A2>100,A2*0.1,A2*0.05)
Exercise 5:
=IF(A1>A2,"A1 is greater than A2","A1 is not greater than A2")The result, “A1 is not greater than A2”, should appear in cell A11.