How to use IF function in Excel

How to use IF Statement in Excel

It’s been a long time since I have not written any blog. This time I have been getting many queries regarding the IF statement in excel. Though this is a very basic formula of excel, still it is a very powerful one. This formula comes under the category of logical formulas and therefore, it can help in creating great logic for calculation. I have been teaching this formula to almost every student I have. Though this is basic formula still it creates a lot of confusion in everybody's mind. So, here I am sharing its step by step implementation and different forms of it for better understanding.

IF statement or you can say IF formula comes under the category of LOGICAL formulas. IF statement has a syntax with three parameters:-

1.       Logical Test

2.       Value If True

3.       Value if False

=IF(LOGICAL_TEST, VALUE_IF_TRUE, VALUE_IF_FALSE)

Out of these, the first parameter is an essential one, and the rest two are optional. But, before working on this formula you need to understand the logical operators being used in excel. So, let’s have a look at the logical operators, these are somewhat similar to those logical operators which are used in mathematics.

Operators	Meaning
=	Equals to
<	Less than
>	Greater than
<=	Less than Equals to
>=	Greater than Equals to
<>	Not Equals to

Now, let’s understand it in a simple way. Every logical operator must have two operands, left-hand-side, and right-hand-side. Every operator applies its logic by comparing left-hand-side operand by right-hand-side operand and return back values in the form of TRUE or FALSE.

Let’s try this in Excel with different operators and operands without using the IF formula.

Try 1:

Try 2:

Try 3:

Here you can see, only logical operators can produce TRUE or FALSE based on operand's value and their comparisons. Now, this is clear from the above examples that if we use IF Formula we can add our choice of output or calculation in parameters, value if true and value if false to create our choice of the result.

Now, let’s make it in a simple IF Statement to generate Pass or Fail using the given criteria:

Criteria: Student will be marked as Pass or Fail using his average percentage with the given slot.

Percentage Slot	Result
0% - 32%	Fail
33% - 100%	Pass

Example

Here, =IF(G2>=33,"Pass","Fail"), the value of G2 has checked whether it is greater than or equals to 33, and if it is true, then the student is given Pass as a result (Value if true), and if it is false, then the student is given Fail as a result (value if false). This type of calculation is called a simple IF Statement.

This IF Statement can become more powerful, if we try and add a nested form to it. To understand the nested form of IF, let’s get back to the criteria of simple IF Statement and make some markings for its simplification. Here, I have divided them into a two-column rule.

In the first column rule, I am using >= sign for logical calculation and using the highest number as the right-hand-side value for comparing average percentage as left-hand-side value. And, if this logical calculation returns TRUE, then its corresponding value "Pass" will be used as value if true parameter otherwise, "Fail" will be used as a value if false parameter.

In the second column rule, I am using <= sign for logical calculation and using the lowest number as the right-hand-side value for comparing average percentage as left-hand-side value. And, if this logical calculation returns TRUE, then its corresponding value "Fail" will be used as value if true parameter otherwise, "Pass" will be used as a value if false parameter.

Here you can see, both rules can return the same output.

Now, let’s use these two columns rule in multiple values to make the nesting form of IF happen.

Criteria: Student will be marked as A Grade, B Grade or C Grade using his average percentage with the given slot.

Percentage Slot	Result
0% - 32%	C Grade
33% - 59%	B Grade
60% - 100%	A Grade

Now, by using the first column rule here, I am using >= sign for logical calculation and using the first highest number as right-hand-side value for comparing average percentage as left-hand-side value. Now, if this the logical calculation returns TRUE, then its corresponding value "A Grade" will be used as a value if true parameter.

And, if this logical calculation returns FALSE, then again I am using >= sign for logical calculation and using the second-highest number as right-hand-side value for comparing average percentage as left-hand-side value. And, if this logical calculation returns TRUE, then its corresponding value "B Grade" will be used as a value if true parameter.

Otherwise, "C Grade" will be used as a value if false parameter.

Similarly, by using the second column rule here, I am using <= sign for logical calculation and using the first lowest number as right-hand-side value for comparing average percentage as left-hand-side value. And, if this logical calculation returns TRUE, then its corresponding value "C Grade" will be used as a value if true parameter.

And, if this logical calculation returns FALSE, again I am using <= sign for logical calculation and using the second-lowest number as right-hand-side value for comparing average percentage as left-hand-side value. And, if this logical calculation returns TRUE, then its corresponding value "B Grade" will be used as a value if true parameter.

Otherwise, "A Grade" will be used as a value if false parameter.