IF Excel Function

IF Function in Excel

IF function in Excel checks whether a condition is met and if it’s (TRUE) it returns a value, and another value if the condition does not meets (FALSE). IF function gives excel formula a decision- making capabilities. This function takes three arguments, each separated by a comma.

IF function is a very useful and most widely used conditional function in excel, this function is used to give a result based on certain criteria; for example, if condition A is met, then the value should be B, and if the condition is not met the value should be C, this function takes three arguments, the first argument is the criteria while the second argument is the result when the condition is true and third argument is when the condition is false.

Syntax

Excel IF Formula

How to Use IF Function in Excel

You can download this IF Function Excel Template here – IF Function Excel Template

Example #1

Let us discuss an example of an IF function.

If there would be no oxygen on a planet, then there will be no life, and there will be oxygen, then there will be a life on a planet.

IF Function Example 1

We have to find out whether there is life possible on the planets given in the list; the condition is there has to be an availability of oxygen; in column B, we have specified whether there is oxygen on a given planet or not.

So, using the IF function, we will find out whether life is possible or not on the planet

IF Function Example 1-1

So, applying the If formula in C2,

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

IF Function Example 1-2

Dragging the IF formula down, we find out that Life is possible on Earth only because there is an availability of oxygen.

IF Function Example 1-3

IF Function Example 1-4

Flow Chart of IF Function

Flow Chart of IF Function1st Case:

Flow Chart of IF Function 1Similarly, there will be the same flow for the IF condition for the 2nd and 3rd cases.

4th Case:

Flow Chart of IF Function 2So, you can see the IF function allows us to make logical comparisons between values. The modus operandi of IF is if something is true, then do something, otherwise do something else.

Example #2

In this example of IF function, if we have a list of years and we want to find out the given year is a leap year or not.

IF Function Example 2

A leap year is a year that has 366 days (the extra day is the 29th of February). The condition to check a year is a leap year or not, the year has to be exactly divisible by 4, and at the same not exactly divisible by 100, then it is a leap year, or if the year is exactly divisible by 400, then it is a leap year.

So, to find the remainder after a number is divided by a divisor, we use the MOD function.

So, if MOD(Year,4)=0 and MOD(Year,100)<>(is not equal to) 0, then it is a leap year

Or if MOD(Year,400)=0, then also it a leap year, otherwise it is not a leap year

So, in excel, the formula to find the leap year would be

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

Where Year is a reference value

IF Function Example 2-1

IF Function Example 2-2

So, after applying the If the formula, we get the list of years that are a leap year, 1960, 2028, and 2148 in the list are the leap year.

IF Function Example 2-3

IF Function Example 2-4

IF Function Example 2-5

So, in the above case, we have used IF Functioning, AND, OR, and MOD function to find the leap year. AND used when two conditions are to be checked as TRUE and OR if either of the condition is to be checked as TRUE.

Example #3

In this example of IF function, logical Operators, and their meaning that is used in IF function best conditions are:

IF Function Example 3

Another example of an IF function, if there is a list of Drivers and there a road intersection, right turns goes to Town B and left turn goes to Town C, and we want to find drivers have their destinations to Town B and Town C.

IF Function (Destination Example)

IF Function Example 3-1

Again, we will use the IF function to find the destination; according to the condition, if a driver makes right turns, he/she reaches Town B, and if he/she makes a left turn, he/she reaches Town C.

So, the IF formula in Excel would be

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

IF Function Example 3-2

Dragging the formula down, we get the destinations of each driver for the turning movement taken.

IF Function Example 3-3

Output:

IF Function Example 3-4

Total 6 Drivers have reached Town C, and the remaining that is 4 have reached Town B.

Example #4

In this example of the IF function, we will be using the excel IF Vlookup function. We have an inventory that contains a list of item and number of the items

Example 4

The name of items are listed in column A and the number of items in column B, and in E2, we have the data validation list containing the entire items list. Now, we want to check whether an item is available in the inventory or not.

Example 4-1

To check that we will use the vlookup along with the IF function, a vlookup function will look up the number of item values, and the IF function will check whether the item number is greater than zero or not.

So, in F2, we will use the If formula in excel.

=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 Item is not available else the item is available.

Example 4-2

If we select any other item in the E2 item list, we can know whether the item is available or not in the inventory.

Example 4-3

Nested IF:

When IF function is used inside another IF formula in excel, this is known as the Nesting of IF function. If there are multiple conditions that need to be fulfilled, in that case, we have to use the Nested IF.

Nesting of IF function in excel syntactically could be written as

IF( condition1, value_if_true1, IF( condition2, value_if_true2, value_if_false2 ))

Example #5

In this example of excel IF function, We have a list of students and their marks, and we have the grade criteria depending upon the marks obtained by the student, and we have to find the grade of each student.

Example 4-4

We will use the If conditions to find the grade of the student, we will be using the Nested IF in excel that is if inside IF conditions since we have multiple criteria to decide the grade of each student.

Example 4-5

We will use multiple IF conditions with AND function we find out the grade, the formula will be

=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”))))

We know IF function checks the logical condition

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

Let’s break this and check,

  • 1st logical test is B2>=95
  • Value_if_true execute:  “A” (Grade A)
  • else(comma) enter value_if_false
  • value_if_false – again finds another IF condition and enter IF condition
  • 2nd Logical Test is B2>=85(logical expression 1) and B2<=94(logical expression 2); since we are testing both, the conditions have to be TRUE, and we have used AND to check the multiple logical expression
  • Value_if_true execute:  “B” (Grade B)
  • else(comma) enter value_if_false
  • value_if_false – again finds another IF condition and enter IF condition
  • 3rd Logical Test is B2>=75(logical expression 1) and B2<=84(logical expression 2); since we are testing both, the conditions have to be TRUE, and we have used AND to check the multiple logical expression
  • Value_if_true execute:  “C” (Grade C)
  • else(comma) enter value_if_false
  • value_if_false – again finds another IF condition and enter IF condition
  • 4th Logical Test is B2>=61(logical expression 1) and B2<=74(logical expression 2); since we are testing both, the conditions have to be TRUE, and we have used AND to check the multiple logical expression
  • Value_if_true execute:  “D” (Grade D)
  • else(comma) enter value_if_false
  • value_if_false execute: “F” (Grade F)
  • Closing Parenthesis

Example 4-6

Example 4-7 Example 4-8

Things to Remember

  • Use Nesting if function to a limited extent because multiple if statements require a great deal of thought to build them accurately.
  • Whenever we use multiple IF statements, it requires multiple open and closing parentheses (), which often becomes difficult to manage. Excel provides the best way to deal with this situation, check for the color of each opening and closing parenthesis; the last closing parenthesis color would always be black, that denotes the formula statement ends there.
  • Whenever we pass a string value, for value_if_true and value_if_false argument, or we test a reference against a string value that always has to be in double quotes, simply passing a string value without quotes would result in #NAME? error

IF Excel Function Video

Recommended Articles

This has been a guide to IF Function in Excel. Here we discuss the If Formula in excel with example and how to use the IF function along with Example of If Function and downloadable excel templates. You may also look at these useful functions in excel

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Reader Interactions

Leave a Reply

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