Many friends requested me to write about if function and it’s use with practical example. So this Sunday, I gather all my thoughts together and wrote about if function. I hope, you would love it.

Whether you are student or employee or CEO of the company, you must know about if function if at all you use excel. Come on, if function is like basic necessity of excel user. 😛

When you want to put logical argument in the excel…like if this then X and if not then Y, Excel if function comes as a life saver function.

If statements checks the logical arguments and based on that argument the function provides the value of true or false.

Syntax of If function

=if(Logical test, [Value if true], [Value if false])

Arguments of the If function

Logical test: Here, we required to insert logical argument, based on which excel will check the logic and returns true value if the logical arguments are satisfied and value of false if not.
[Value if true]: Here, we have to write the value we want on fulfillment of conditions laid in logical test.
[Value if false]: Here, we have to put the value we want on failure of logical test.

Points to be considered while using if:

1. Non- numeric criteria has to be written with double inverted comma (e.g. “James”)
2. Numeric criteria can be written without double inverted comma.
3. Wildcards like ? and * can be used in if formula.
   “?” stands for one character while,
   “*” stands for one or more characters.

Practical use of if function

Let’s take a basic example to understand the if function of excel. Here is the student’s result of exam. If student obtains equal to or more than 50%, he should be declared as “Pass”, otherwise “Fail”

Here is the Practice file for if function to practice with me. So download it and let’s start.

Solution 1: Using If function

=if(Logical test, [Value if true], [Value if false])

=if(F4/400>=0.5,”Pass”,”Fail”

Output : “Pass”

Here in our above example

F4/400>=0.5 total marks obtained divided by total marks will give you %. Here we have use logical test like if total % is equal to or greater than

“Pass” If student is pass, we want “Pass” as an outcome

“Fail” If student obtained less than 50%, cell should show “Fail”

Now, drag down the formula by selecting all 4 cells from G4:G7 and press Ctrl + D. You will see that 3^rd student has not cleared exam yet.

Evaluation of If formula:

Here, if function test the logical argument given by you on the cells specified by you. If all the logical test is cleared by that set of cells, if function gives you “Value if true”, otherwise “Value if False”.

“Wow, learned how to use logical test in if. But what if we want to evaluate by two logical test.” Say like student must have obtained 40 marks in individual subject and overall 50%? Here, If and And as well as Nested If Function come into picture.

More than one logical test:

Let’s take again the same example of students’ results stats. Now we have to apply the nested if as we have to use more than one if statements to get out desired results.

Passing Criteria = Individual 40 Marks and overall 50%

Solution 1: Nested If Function

Alternatively above arguments can be put up through multiple if instead of using and function.

1. Let’s start by writing first logical test in if function. If(b5>=40,
2. If this (b5>=40) is true, we want excel to check another logical test. So write the second condition in value if true by using additional if . If(B5>=40,if(C5>=40
3. Again, we will check another condition on success of 2^nd one. So write if again and again like this till all of your condition is fulfilled.
   
4. Now press comma to write what if all conditions are fulfilled, write “Pass” after comma.
5. Again put a comma to enter what if last condition is not fulfilled. Write “Fail”
   
6. Now close the bracket and put a comma and write “Fail” for 4^th condition, again close the bracket put a comma and write “Fail” for 3^rd,2^nd and 1^st condition. Press Enter.

Boom!! You have got your results by using nested if for more than one logical test. Drag the cell to down in other 3 students.

Solution 2: Using And function with If function

In some cases, even you can use AND/OR with IF completely replacing nested if to make your work simpler. Here is how,

1. Write If function =if(
   Here, we have to compare total 4 criteria, so we need to use and because if all the criteria are fulfilled then only student is pass.
2. Write and in the if formula =if(and(
3. Here, we can put individual subject criteria as well as overall criteria separated by commas. +if(and(b5>=40,c5>=40,d5>=40,e5>=40,f5/400>=0.5
4. Close the bracket and put comma to go into value of true argument of if function as whole and is used in logical test.
   
5. Now it’s simple, write value if true = “Pass” and “Fail” in value if false.
   

   =if(Logical test, [Value if true], [Value if false]) =IF(AND(B5>=40,c5>=40,d5>=40,e5>=40,f5/400>=0.5),”Pass”,”Fail”) Output : “Pass” for A, and “Fail” for B,C & D.

Here in above example,
AND(B5>=40,c5>=40,d5>=40,e5>=40,f5/400>=0.5) logical test to check all the conditions.
“Pass” If student is pass, we want “Pass” as an outcome
“Fail” If student obtained less than 50%, cell should show “Fail”

Now, drag down the formula by selecting all 4 cells from G5:G8 and press Ctrl + D.

Here is the IF Practice File to check your understanding about IF function : File

Question of the Week

The team of Simplified Excel brings to you “The Question of the Week.”

• You can drop your answer with little explanation in comment box.
• Person giving simplest answer would be declared as winner on the next blog.

Here is the question of the week…

If the sales is made in the month of April, answer should be “M04”. If May then “M05” and “M06” for June month sales.

You can try the same and write your answer in comment box. Here is the Question of the Week – If function. If you are confused about that your answer is correct or not, put your answer in comment box…no one is perfect and you need not to be. So start commenting!!

Don’t forget to share and subscribe our blog via email, if you loved post.
Have any suggestion/ recommendation? Leave a comment.