Using “IF” Function in Excel

In our day to day lives we come across different situations where we need to choose/ make a decision based on the options available. Every decision we make/ need to make will be an “IF” statement.

  • Do I have the Sales data for the month? IF yes, I can prepare the dashboard.
  • IF the meeting is completed, then I can leave the office early.
  • IF I leave the office early, I can go to movie with my GirlFriend. J

Is an IF statement really necessary in every situation

The answer is no. You can do the comparisons directly in excel by using the comparison operators. Excel returns either “True” or “False” based on the operator you used. See the below images to understand.


Comparison Operators

Operator

Function

=

Equal to

<>

Not Equal to

>

Greater than

<

Less than

>=

Greater than or Equal to

<=

Less than or Equal to

When to Use the IF Function

If you want to return a value other than “True” or “False” we use the “IF” function.

The “IF” Function

“IF” function is one of the most widely used functions. “IF” function is known as logical condition statement. It is used to test a condition and return a value based on whether the condition is met or not met.

How to Write IF Function in Excel

Any formula/ function has a specific way of writing which is called as the syntax. Unless you follow the syntax the function will not give you the desired/ correct/ accurate results.

The Syntax for IF is as follows:

=IF(Logical Test/ Condition, Value if condition is True, [Value if condition is False])

IF Function takes three arguments

  1. Logical Test/ Condition: A value or condition which can needs to be checked, could be either true or false based on which the return value depends.
  2. Value if True: The value to be returned if the logical condition is met/ satisfied.
  3. Value if False (Optional): The value to be returned if the logical condition is not met.

Value if True and Value if False can be

  1. A Fixed Value such as number/ text. Any value that needs to be returned as is for condition being True or False should be given in ” “.
  2. A reference to another cell
  3. A formula with another function or combination of multiple functions

Let’s see an example to understand the IF function in detail

Imagine, you need to declare the results of an exam where a student is considered to be passed in the exam if he scores 65 marks or more than 65 Marks else the result should be fail. See the below 2 images to understand how to write IF condition to the case mentioned.


Excel If Function

Evaluating the above example.

Marks Scored are in column B. Hence our logical condition will be based on column B.

Logical Condition will be B2>=65 (as marks should be more than or equal to 65)

Pass should be returned if the marks are >=65. Hence the word “Pass” is placed in 2nd argument (Value if true)

Fail should be returned if the marks are <65. Hence the word “Fail” is placed in 3rd argument (Value if false)

Note that the words Pass/ Fail are given in ” “. Excel returns the value in between ” ” as is. In case if you want to perform a different operation instead of returning a number or word use it without quotes.

How to give a cell reference or formula in IF function

Let’s use the above example only, where the pass condition remain the same but you need to find out How many marks the student is short of pass mark in case of Fail and display the same instead of Fail.


The above image shows the usage of IF with reference to another cell and a formula in True and False conditions.

Let’s evaluate the formula

Logical Condition is the same, will be B2>=65 (as marks should be more than or equal to 65).

Pass should be returned if the marks are >=65. “Pass” is in cell B14 which is used to display if the student scored marks >=65. Hence the cell reference B14 is used in 2nd argument (Value if true).

Difference in Marks should be returned in case if marks are less than 65. Hence instead of fail 65 – B2 (marks scored) is used in 3rd argument (Value if false).

The marks scored by Student A is 67 (cell B2). The condition, B2>=65 is True, hence the true argument B14 , which is “pass” is displayed in result column. Similarly, in case of Student D is 52 (Cell B5). The condition, B2>=65 will be false, hence the False argument which is 65 – B5 i.e 65 – 52 =13 is displayed in result column.

Download the Practice Workbook used in Example

Do share this post with your friends if you find it useful.


Leave a Reply