Excel IF function With Examples

The IF function is one of Excel’s most used, if not the most used functions. It helps perform a single or a series of logical comparisons between entities. It works using the same principles as IF-ELSE conditional statements, used in most programming languages. Values satisfying the given condition, result in a positive output, while values not satisfying the given condition, result in a negative output. Condition and Return values both can be decided by the user.

Python Code (For reference)

IF Function in Excel

The IF Function is an inbuilt function in Excel, which return values based on true or false condition.

Syntax of IF Function

The IF function serves a single purpose, i.e., to run tests for specific conditional statements. 

Syntax:  If(logical_argument,[Value_if_true],[Value_if_False])

Return Value

IF function will return the values, “TRUE” or “FALSE” or the values specified by the user to be used in place of TRUE and FALSE.

Parameters

The term argument refers to any expression that is enclosed inside the parenthesis of a function call.

The IF function in Excel consists of three main arguments. They are as follows:

1. logical_argument – This is the argument that defines the condition the user wants to check. 

2. value_if_true – The value to be returned when the above condition returns TRUE (Optional, as default is TRUE).

3. value_if_false – The value to be returned when the above condition returns FALSE (Optional, as default is FALSE). 

The IF function supports the logical operators( <,>,<=,>=,=)  to test logical arguments.

How to Use the IF Function in Excel

Check for the empty cell value

There will be some cases in which the user has to find the rows or columns which is empty but applying the formula manually is not possible for hundreds of cells so we can use the IF function.

The formula used is:= if (logical argument=””,””, “value”)

Let’s consider the below example, In this example, we have data on customers purchasing items and we have the number of items. To find the total amount of Customers we have used the formula = IF(C2=”,””, C2*D2) is checking values for C2 and if C2 is empty it will return empty, and if C2 has a value it will return C2* D2.

IF Function (Greater than)

Let’s say you want to find out if all the students in a particular class PASSED or FAILED their tests. But, we can’t do that manually for 100 students. So, we can define the IF condition for one cell and apply it to all the cells we want.

APPLYING TO ONE CELL

Now, let’s apply it to all the cells in the column.

ALL CELLS

Now, let us try using IF with a bit more complex condition. Say, you have a list of clients, and who have made purchases from your store. Now, according to your store policy, you can provide a discount of Rs.500 to all clients that make purchases strictly above Rs.6,000. So, let’s add that condition to our sheet.

Some clients are eligible for Discounts while some are not.

Nested If Function

We use the Nested IF function if we have multiple criteria to find and decide the final output.

The Nested if the function uses the If function inside another if function when multiple conditions are to be fulfilled. In Simple language, it can be defined as the combination of multiple if functions in one formula.

Syntax

=IF( condition1, Value if true1 ,IF(Condition2, Value if true2, Value if false2)). 

Below is an example of the Nested if function.

Let’s take a dataset of students with their marks and we have to assign the grades based on the marks.

Formula used is : =IF(B2>=90,”A”,IF(B2>=80,”B”,IF(B2>=70,”C”,IF(B2>=60,”D”,”F”))))

Here, Multiple conditions are being checked.

Order of Nested if Function

In the above example, there were four logic tests. When the difficulty level of the logic test is equal then it does not matter which condition should be checked first.

But when the logic test has a different difficulty level, then the order of the condition matters. 

Let’s understand the concept of Logical test order of difficulty from the example below. 

Harder test first 

The formula used is =If(B2>=60,”1st division”, if(b2>=30,”2nd division”, if(B2>=10″3rd division”, “Fail”)))

The marks in B2 are greater than 60 so the result is “1st division” which passes the first logic test.

Easier test first

The formula used here is =IF(B2>=30,”2nd division”,IF(B2>=360,”1st division”,IF(B2>=10,”3rd division”,”fail”)))

Note: This is giving the incorrect result as B2 is 87 so it should give 1st division as the result.

IF Function with AND, OR, NOT

IF function, logical operators, help you to deal with complex conditions.

IF with AND 

AND allows you to check multiple conditions with the if function.

Syntax: IF(AND(condition 1, condition 2), Value if true, Value if false)

For example, there are two conditions A1>90, and B1>90, If both conditions are true then it will return “A1 Grade” otherwise “fail”.

Formula used is : =IF(AND(A1>90,A1<100),”A1 Garde”, “Fail”)

IF with OR

OR allows you to check at least one condition is True within the IF function.

Syntax: IF(OR(condition 1, condition 2 ), Value if true, Value if false)

For example: If you have two conditions that A1>90, B1>90 If at least one of them is true then it will return “Blue” otherwise “Red“.

Formula used is: IF(OR(A1>90,B1>90),”Blue”, “Red”)

IF with NOT

NOT allows you to check a function that negates a condition. It means it will Return “True” if the condition is False and “False” if the condition is True.

Syntax: IF(NOT(Condition 1), Value if true, Value if false)

For example: If you want to check a condition for the color of a ball. If the color of the ball is not red then it will be selected otherwise it will be rejected.

Formula used is: IF(NOT Red), “Selected”, “Rejected”. 

Excel IF Function Multiple Conditions

IF Formula with multiple criteria based on the AND / OR logic. Consequently, in the logic test of your IF formula, you can use one of these functions:

AND Function: returns True if all the given Conditions are met; False otherwise.

OR Function: returns True if any single Condition is met; False otherwise.

FAQs

What is the IFS function?

Instead of using multiple Nested if functions, IFS can be used.

• You can test multiple conditions, to see if they are TRUE.
• The Formula returns the result for the First True condition.

The syntax for the IFS function is: = IFS([Something is true 1, Value if true 1, Something is true 2, Value if true 2, Something is true 3, Value if true 3).

What if our IF cell contains a specific cell?

We can use ISNUMBER FUNCTION or SEARCH FUNCTION  together to create a logic test for a substring. Because IF FUNCTION can’t check for a specific substring as it does not support wildcards.

For Example IF(ISNUMBER(SEARCH(“text'”, A1)), “YES”, “NO”)

What does the IF function do?

The IF function in Excel is a logical text function that can be used to test or check any condition. It returns the first value of the condition if the condition is true otherwise it returns the second value.

Is IF Function Case Sensitive?

No, the IF function in Excel is not Case-Sensitive. It will return the same value for ” text” or “TEXT”.