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.
Syntax:
=IF(logical_test,
[value_if_true], [value_if_false])
Arguments:
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:
=IF(C6>=7,''Pass'', ''Fail'')
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.
0 comments:
Post a Comment