Excel IF function is a logical function that runs a logical test and returns one value for a TRUE result, and another for a FALSE result. For example, to “pass” scores above 60: =IF(A1>60,”Pass”,”Fail”). More than one condition can be tested by nested IF statement. The IF function can be combined with logical functions like AND and OR to extend the logical test. We Shall see in detail about the Syntax, arguments, and Output of the Excel IF function along with details about some common Errors encountered.
Excel IF Function
The IF Function Tests for a specific condition and returns values you supply for a TRUE result and a FALSE result.
Syntax
=IF(logical_test,[value_if_true],[value_if_false])
- logical_test – A value or logical expression that can be evaluated as TRUE or FALSE.
- value_if_true – [optional] The value to return when logical_test evaluates to TRUE.
- value_if_false – [optional] The value to return when logical_test evaluates to FALSE.
Logical Tests
Logical operators (=,<>,>,<) can be used with IF function while writing logical tests inside it. IF function does not support wildcards. However, there is a workaround. You can combine IF with COUNTIF to get the wildcard functionality.
Pass of Fail Example
Suppose the pass mark in a test score is 60 or above, and it is present in the cell A5. Then to evaluate Pass or Fail in the test, we can use IF function as below:
=IF(A5>=60,”PASS”,”FAIL”)
Nested IF Statements
A “Nested IF” refers to a formula where at least one IF function is nested inside another IF function in order to test for more conditions and return more possible results.
=if(A5<60,”Fail”,if(A5>80,”Distinction”,”Pass”))
The above is a case where, if the test score if less than 60, its a FAIL, however, if the score is greater than 80 its a DISCTINCTION and if the score if between 60 and 80, then its a PASS. This can be achieved by writing Nested IF Statements as above to check the test score and display the status accordingly.
IF Function with AND, OR, NOT Functions
IF with AND
If Function can be combined with AND function to return a value when two or more conditions are TRUE, and return another value when atleast one of the conditions is FALSE.
For example to return “Good”, when A5 is between 70 and 80, we can write the formula as below:
=if(AND(A5>70,A5<80),”Good”,””)
IF with OR
If Function can be combined with OR function to return a value when at least one of the conditions is TRUE, and return another value when all of the conditions are FALSE.
Suppose you have to return “Good”, if A5>70 or B5 is >60, and return “Not Good” if both the conditions fail, then you can write the formula as below
=if(OR(A5>70,B5>60),”Good”,”Not Good”)
IF with NOT
If Function can be combined with NOT function to return a value when a conditions is FALSE.
If we want to display “PASS” for any value that is greater than 60, and “FAIL” for any value that is less than 60, we can write the formula as below:
=if(NOT(A5<60),”PASS”,”FAIL”)