IF Excel Function

IF function in Excel evaluates whether a given condition is met and returns a value depending on whether the result is “true” or “false”. It is a conditional function of Excel, which returns the result based on the fulfillment or non-fulfillment of the given criteria.

For example, the IF formula in Excel can be applied as follows:

 “=IF(condition A,“value B”,“value C”)”

 The IF excel function returns “value B” if condition A is met and returns “value C” if condition A is not met.

It is often used to make logical interpretations which help in decision-making.

The syntax of the IF function is shown in the following image:

The IF excel function accepts the following arguments:

• Logical_test: It refers to the condition to be evaluated. The condition can be a value or a logical expression.
• Value_if_true: It is the value returned as a result when the condition is “true”.
• Value_if_false: It is the value returned as a result when the condition is “false”.

In the formula, the “logical_test” is a required argument, whereas the “value_if_true” and “value_if_false” are optional arguments.

The IF formula uses logical operators to evaluate the values in a range of cells. The following table shows the different logical operators and their meaning.

Let us understand the working of the IF function with the help of the following examples in Excel.

Example #1

If there is no oxygen on a planet, life is impossible. If oxygen is available on a planet, then life is possible. The following table shows a list of planets in column A and the information on the availability of oxygen in column B. We have to find the planets where life is possible, based on the condition of oxygen availability.

Let us apply the IF formula to cell C2 to find out whether life is possible on the planets listed in the table.

The IF formula is stated as follows:

“=IF(B2=“Yes”, “Life is Possible”, “Life is Not Possible”)

The succeeding image shows the IF formula applied to cell C2.

The subsequent image shows how the IF formula is applied to the range of cells C2:C5.

Drag the cells to view the output of all the planets.

The output in the below worksheet shows life is possible on the planet Earth.

Flow Chart of Generic IF Excel Function

The IF Function Flow Chart for Mars (Example #1)

The flow of IF function flowchart for Jupiter and Venus is the same as the IF function flowchart for Mars (Example #1).

The IF Function Flow Chart for Earth

Hence, the IF excel function allows making logical comparisons between values. The modus operandi of the IF function is stated as: If something is true, then do something; otherwise, do something else.

Example #2

The following table shows a list of years. We want to find out if the given year is a leap year or not.

A leap year has 366 days; the extra day is the 29th of February. The criteria for a leap year are stated as follows:

1. The year will be exactly divisible by 4 and not exactly be divisible by 100 or
2. The year will be exactly divisible by 400.

In this example, we will use the IF function along with the AND, OR, and MOD functions to find the leap years.

We use the MOD function to find a remainder after a dividend is divided by a divisor.

The AND function evaluates both the conditions of the leap years for the value “true”. The OR function evaluates either of the condition for the value “true”.

We will apply the MOD function to the conditions as follows:

If MOD(year,4)=0 and MOD(year,100)<>(is not equal to) 0, then the year is a leap year.

or

If MOD(year,400)=0, then the year is a leap year; otherwise, the year is not a leap year.

The IF formula is stated as follows:

“=IF(OR(AND((MOD(year,4)=0),(MOD(year,100)<>0)),(MOD(year,400)=0)),“Leap Year”, “Not A Leap Year”)”

The argument “year” refers to a reference value.

The following images show the output of the IF formula applied in the range of cells.

The following image shows how the IF formula is applied to the range of cells B2:B18.

The succeeding table shows the years 1960, 2028, and 2148 as leap years and the remaining as non-leap years.

The result of the IF excel formula is displayed for the range of cells B2:B18 in the following image.

Example #3

The succeeding table shows a list of drivers and the directions they undertook to reach the destination.  It is preceded by an image of the road intersection explaining the turns taken by the drivers and their destinations. The right turn leads to town B, and the left turn leads to town C. Identify the driver’s destination to town B and town C.

Road Intersection Image

Let us apply the IF excel function to find the destination. Here, the condition is mentioned as follows:

• If the driver turns right, he/she reaches town B.
• If the driver turns left, he/she reaches town C.

We use the following IF formula to find the destination:

 “=IF(B2=“Left”, “Town C”, “Town B”)”

The succeeding image shows the output of the IF formula applied to cell C2.

Drag the cells to use the formula in the range C2:C11. Finally, we get the destinations of each driver for their turning movements.

The below image displays the IF formula applied to the range.

The output of the IF formula and the destinations are displayed in the succeeding image.

The result shows that six drivers reached town C, and the remaining four have reached town B.

Example #4

The following table shows a list of items and their inventory levels. We want to check if the specific item is available in the inventory or not using the IF function.

Let us list the name of items in column A and the number of items in column B. The list of data to be validated for the entire items list is shown in the cell E2 of the below image.

We use the Excel IF along with the VLOOKUP function to check the availability of the items in the inventory.

The VLOOKUP function looks up the values referring to the number of items, and the IF function will check whether the item number is greater than zero or not.

We will apply the following IF formula in the F2 cell:

“=IF(VLOOKUP(E2,A2:B11,2,0)=0, “Item Not Available”,“Item Available”)”

If the lookup value of an item is equal to 0, then the item is not available; else, the item is available.

The succeeding image shows the result of the IF formula in the cell F2.

Select “bat” in the E2 item cell to know whether the item is available or not in the inventory (as shown in the following image).

Example #5

The following table shows the list of students and their marks. The grade criteria are provided based on the marks obtained by the students.  We want to find the grade of each student in the list.

We apply the  Nested IF in Excel since we have multiple criteria to find and decide each student’s grade.

The Nesting of IF function uses the IF function inside another IF formula when multiple conditions are to be fulfilled.

The syntax of Nesting of IF function is stated as follows:

“=IF( condition1, value_if_true1, IF( condition2, value_if_true2, value_if_false2 ))”

The succeeding table represents the range of scores and the grades, respectively.

Let us apply the multiple IF conditions with AND function in the below-nested formula to find out the grade of the students:

“=IF((B2>=95),“A”,IF(AND(B2>=85,B2<=94),“B”,IF(AND(B2>=75,B2<=84),“C”,IF(AND(B2>=61,B2<=74),“D”,“F”))))”

The IF function checks the logical condition as shown in the formula below:

“=IF(logical_test, ,)”

We will split the above-mentioned nested formula and check the IF statements as shown below:

First Logical Test: B2>=95

If the formula returns,

• Value_if_true, execute: “A” (Grade A) else(comma) enter value_if_false
• Value_if_false, then the formula finds another IF condition and enter IF condition

Second Logical Test: B2>=85(logical expression 1) and B2<=94(logical expression 2)

(We use AND function to check the multiple logical expressions as the two given conditions are to be evaluated for “true.”)

If the formula returns,

• Value_if_true,  execute: “B” (Grade B) else(comma) enter value_if_false
• Value_if_false, then the formula finds another IF condition and enter IF condition

Third Logical Test: B2>=75(logical expression 1) and B2<=84(logical expression 2)

(We use AND function to check the multiple logical expressions as the two given conditions are to be evaluated for “true.”)

If the formula returns,

• Value_if_true,  execute: “C” (Grade C) else(comma) enter value_if_false
• value_if_false, then the formula finds another IF condition and enter IF condition

Fourth Logical Test: B2>=61(logical expression 1) and B2<=74(logical expression 2)

(We use AND function to check the multiple logical expressions as the two given conditions are to be evaluated for “true.”)

If the formula returns,

• Value_if_true, execute: “D” (Grade D) else(comma) enter value_if_false

• Value_if_false, execute: “F” (Grade F)
• Finally, close the parenthesis.

 The below image displays the output of the IF formula applied to the range.

The succeeding image shows the IF nested formula applied to the range.

The grades of the students are listed in the following table.

The guidelines for the multiple IF statements are listed as follows:

• Use nested IF function to a limited extent as multiple IF statements require a great deal of thought to be accurate.
• Multiple IF statements require multiple parentheses (), which is often difficult to manage. Excel provides a way to check the color of each opening and closing parenthesis to avoid this situation. The last closing parenthesis color will always be black, denoting the end of the formula statement.
• Whenever we pass a string value for the arguments “value_if_true” and “value_if_false” or test a reference against a string value, enclose the string value in double quotes. Passing a string value without quotes will result in “#NAME?” error.