The IF function is one of the most common and useful functions in excel. It is categorized as a logical function. It allows you to make your own condition and return one value if condition is true and another value if condition is false. The IF function can be used to evaluate both text and numeric values. Double quotation marks must be used for any text criteria or criteria that includes logical or mathematical symbols and you can use the numeric value without double quotation when applying IF function e.g. IF (A1=”Yes”, 2, 3) or IF (A1=12, “Yes”, “No”).
What is awesome about IF function is that it let you combined with other functions like AND and OR to meet your different requirements.
In this tutorial we will learn the syntax and then look some examples of IF function.
=IF(logical_test, [value_if_true], [value_if_false])
Logical_test - It refers to the expression to be evaluated as TRUE or FALSE. Here you can specify a numeric value, text value, date or any comparison operator (=, >, <, >=, <=, <>).
Value_if_true - [optional] It is the value that is returned if logical_test evaluates to TRUE.
Value_if_false - [optional] It is the value that is returned if logical_test evaluates to FALSE.
Example 1 - Showing Result
Suppose you are going to take a test from students. And you have made a condition if a student get 7 marks or above, he will be pass otherwise he will be fail, if he get below 7 marks. Let’s see the example.
The formula in cell D6 is:
In the above example, excel returned the value “Pass” to A, C, and D because they got 8, 9 and 10 marks respectively. On the other hand, excel returned the value “Fail” to B and E because they got 6 and 5 marks respectively. It is just because of using IF function.
Example 2 - Finding Increased Value
In this example we are going to check the increased value by using IF function. For this we will draw a table containing columns value 1, value 2 and increased value. In the table value1 and value 2 will be drawn by us and increased value will be return by excel.
The formula in cell D6 is:
=IF(C6>B6, C6-B6, C6-B6)
In the above table we can see that excel returned some positive and negative values because we made a condition if C6>B6, then return the value = C6-B6 otherwise return the value = C6-B6. In the table positive value indicating the increased value and negative indicating the decreased value.
Example 3 - Giving Bonus to Employees
In this example we are going to give bonus to employees. For this we will make a simple condition. The condition is if an employee make sale greater than 800, he will be provided 5 percent (5%) bonus on his salary, otherwise he will be advised to try again. So let’s try it.
The formula in cell E6 is:
=IF(D6>800, C6*5%, ''Try again'')
It can be seen from the above table that the employees having sale greater than 800 are given 5% bonus on their salaries. Conversely, the employees that having sales less than 800 are advised to try again. I just used IF function in cell = E6 and drag it to below to apply the same function to all other cells. You also can use the same way for saving time.
Example 4 - Finding Unequal Value
In this example we will find the unequal value in the table. In order to find unequal value we will draw a table containing some random values. Then we will make a condition if any value is equal to 10 will be considered WRONG, otherwise OK, if any value is not equal to 10. See the given below example.
The formula in cell C6 is:
=IF(B6<>10, ''OK'', ''Wrong'')
You can see from the above table that excel returned ‘OK’ to all those values which are not equal to value 10. On the other hand, excel returned ‘WRONG’ to all those values which are equal to value 10. This is the magic of IF function.