IF comes in the category of logical functions. IF function can be used for logical comparison of values and to get the first result when the comparison is TRUE and second result when the comparison is FALSE. It is evaluated by putting various logical operators like “=”,”>”,”<” etc. in the formula .IF function is also used in combination with other functions like AND , OR , COUNT etc. More than one function can be tested with the help of nested IF formula.
=IF(logical_test, [value_if_true], [value_if_false])
- logical_test – This is the parameter which will be evaluated as TRUE or FALSE
- value_if_true – [optional] Here we will enter the value which will be shown when test is true.
- value_if_false – [optional] Here we will enter the value which will be shown when test is false.
When using IF Function, the following logical operators can be used
|>=||Greater than or equal to|
|<=||Less than or equal to|
|<>||Not equal to|
- In case logical test returns TRUE and the result for TRUE is omitted, IF function returns “0” as output
- In case logical test returns FALSE and the result for FALSE is omitted, IF returns FALSE as output
- IF is case insensitive when we compare the text.
- If any of the arguments to IF have been used as arrays, the IF function can measure each part of the array.
- To count things with conditions, use the COUNTIF or the COUNTIFS functions.
- To add things with condition, use the SUMIF or the SUMIFS functions.
- Nested IF statements: – The IF Logic could be “nested”. A “nested IF” is a sub type of IF in we nest at least one value of IF function within another so as to check for extra conditions and get other results.
- Up to 64 IF’s can be nested in one formula.
In this example, we are comparing the payment amount in column C to the total amount in column B. If the paid amount is higher than the total amount, then IF function gives a value of “Overpaid.” If the paid amount is not higher than the total amount, then IF function gives a value of “Not Overpaid.”
Nested IF Example: – In nested IF formula we can check multiple conditions and return the different results depending on the different conditions. In this example if the age is greater than 60 the result should be “Sr. Citizen” and if age is less than 18 the result should be “Child” else the result should be “Adult”.