IF Function

IF Function in Excel

IF is widely used function in excel. IF function in excel help us to make logical comparisons between value and what we expect.

For comparison test with IF function we can use logical comparators. There are six logical comparators in excel. Please find below summary of logical comparators.

Logical comparator Meaning
= Equal to
> Less than
< Greater than
>= Greater than or equal to
<= Less than or equal to
<> Not equal to

IF: Return one result if TRUE, another if FALSE.


IF Formula Syntax :

=IF (logical_test, [value_if_true], [value_if_false])

Arguments

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.


Tutorial of IF formula in Excel

Example 1: 

Explanation: –

In the above example, we have a list of students with their score. If we want to set two class out of all named “FAIL” & “PASS” For that, we set a function IF returns “FAIL” else it returns “PASS”.


Multiple IF statement in Excel (Nested IF Function)

It is possible to use multiple IF statement in excel in one formula. We can use 7 numbers of IF statement in one formula.

Example of Multiple IF statement in Excel

Multiple IF Statement

In above example we start testing to see if score is below 51, IF true returns “E”, IF FALSE we move into the next IF function. Now we will test if score is less than 61, IF true returns “D”. IF FALSE we move into the next IF function. IF score is less than 71. IF true return will be “C”. IF FALSE we move into next IF FUNCTION, IF score is less than 81, return will be “B”, IF FALSE we need “A”.

Note: – In this case it is important to move in one direction only, either from High to low & Low to high or high to low. IF function returns a result whenever test returns True.


IF function with Logical test- IF(OR)

Let’s calculate commission of Sales person based on Sales Goal and Number of accounts. Here Sales person either need to exceed sales Goal OR number of account goal to earn commission.

Since Robert has Total sales $ 11,000 >= Sales Goal $ 7,000, OR number of accounts 6> Account Goal 5, he is eligible for Bonus. Steeve is neither meeting Sales Goal nor meeting number of account Goal not eligible for Commission.


IF function with Logical tests- IF(AND)

Similarly calculate incentive of Sales person based on Sales Goal and Bonus Goal. Here Sales person need to exceed sales Goal AND Bonus Goal to Earn Bonus.

Robert is eligible for Bonus as he is meeting both conditions

i.e.

(i)Total sales ($11,000)>= Sales Goal ($7,000)

(ii) Total Sales ($ 11,000)>= Bonus Goal ($10,500)

Recommended For You

About the Author: Mehul Vaghela

Howdy Math fan. I'm Mehul Vaghela - B.E.Mechanical. Enthusiastic and holds the passion of reading, writing, and learning. In 10 years of experience on the field, I've faced so much problem with managing data and learned to make that happen. ExcelElixir is one of the parts of passing it on (knowledge).

Leave a Reply

Your email address will not be published. Required fields are marked *