In this post let us see how to use the if function in excel with practical examples.
DOWNLOAD the practice workbook to follow along.
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 over, then I will leave the office early.
- IF I leave the office early, I can go to movie with my Girl Friend.
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 options. Excel returns either “True” or “False” depending on the comparison option you choose.
See the below images to understand.
Comparison Operators
The different options for comparison in excel are as follows
| Operator | Description | Used for |
| > | Greater Than | Compare greater of the given 2 values |
| < | Less than | Compare lesser of the given 2 values |
| = | Equal to | Whether two values are equal |
| <> | Not Equal To | Whether two values are equal or not |
| >= | Greater than or Equal to | Compare whether one value is greater or equal to other |
| <= | Less than or Equal to | Compare whether one value is lesser or equal to other |
When to Use the IF Function in Excel
As mentioned, if the comparison operators returns us the result of True or False then what is the need or why should we use If function in excel.
While, the comparison operator returns true or false, it cannot provide or validate any other logic and return the values accordingly.
Hence, to get the required or desired result other than True or False after validating the logical condition(s) we should use the IF function in excel along with the comparison operators.
In short, if you want to return a value other than “True” or “False” we use the “IF” function.
The “IF” Function in Excel
“IF” function is one of the most widely used functions in excel. “IF” function in excel 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.
Syntax for IF Function in Excel
The Syntax for IF is as follows
=IF(Logical Test/ Condition, Value if condition is True, [Value if condition is False])
Arguments for IF Function
IF Function takes three arguments.
Logical Test/ Condition: A value or condition which is to be checked, can be either true or false based on which the return value depends.
Value if True: The value to be returned if the logical condition is met/ satisfied.
Value if False (Optional): The value to be returned if the logical condition is not met.
The arguments Value if True and Value if False can be
- A Fixed Value such as number/ text. Any value that need to be displayed as is for condition being True or False should be given in “ “.
- A reference to another cell.
- A formula with another function or combination of multiple functions
Example for Using IF function in Excel
Let us see an example to understand the excel IF function in detail.
Imagine, you need to declare the results of an exam. 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 .
Evaluating the above example
Marks Scored are in column B. Hence we should write our logical condition using column B.
Logical Condition will be B2>=65 (as marks should be more than or equal to 65)
We Should get result as Pass if the marks are >=65. Hence the word “Pass” should be in 2nd argument (Value if true)
We Should see Fail if the marks are <65. Hence the word “Fail” should be in 3rd argument (Value if false)
Note that the words Pass or Fail are in double quotes (“ “). Excel returns the value in between (double quotes)“ “ 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 Excel IF function
Suggested Read : Know more about different types of cell references and how to use cell reference(s) in excel.
Instead of returning True or False, let us see how to use cell reference or use a different condition in excel if function. As mentioned earlier we should use the formula or cell reference in the value if true or value if false arguments.
Example for Using cell references and formulas in Excel IF function
Let us use the above example only, where the pass condition remain the same but you also need to find out How many marks the student is in short of pass mark in case of Fail and display the same instead of displaying as Fail.
The above image shows the usage of IF with reference to another cell and a formula in True and False conditions.
Let us understand the formula step by step
Logical Condition is the same, will be B2>=65 (as marks should be more than or equal to 65).
Value should be Pass if the marks are >=65. “Pass” is in cell B14 and we can use this to display if the student marks are >=65. Hence the cell reference B14 need to be in 2nd argument (Value if true).
Wherever marks are less than 65 we need to get the difference of marks. Hence instead of fail we have to use “65 – B2” (marks scored) 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.
Practice/ Sample Files
Click the below button to Download the Practice Workbook used in Example
Do not forget to share this post with your friends.