When to use Excel IF Function
IF function in Excel is best suited for situations where you check whether a condition is TRUE or FALSE. If it’s TRUE, the function returns a specified value/reference, and if not then it returns another specified value/reference.
What it Returns
It returns whatever value you specify for the TRUE or FALSE condition.
Syntax
=IF(logical_test, [value_if_true], [value_if_false])
Input Arguments
logical_test – this is the condition that you want to test. It could be a logical expression that can evaluate to TRUE or FALSE. This can either be a cell reference, a result of some other formula, or can be manually entered. [value_if_true] – (Optional) This is the value that is returned when the logical_test evaluates to TRUE. [value_if_false] – (Optional) This is the value that is returned when the logical_test evaluates to FALSE.
Important Notes About using IF Function in Excel
A maximum of 64 nested IF conditions can be tested in the formula. If any of the argument is an array, each element of the array is evaluated. If you omit the FALSE argument (value_if_false), i.e., there is only a comma after the value_if_true argument, the function would return a 0 when the condition is FALSE. For example, in the example below, the formula is =IF(A1>20,”Approve”,), where the value_if_false is not specified, however, the value_if_true argument is still followed by a comma. This would return 0 whenever the checked condition is not met. If you omit the TRUE argument (value_if_true), and specify only the value_if_false argument, the function would return a 0 when the condition is TRUE. For example, in the example below, the formula is =IF(A1>20,,”Approve”), where the value_if_true is not specified (only a comma is used to then specify the value_if_false value). This would return 0 whenever the checked condition is met.
Excel IF Function – Examples
Here are five practical examples of using the IF function in Excel.
Example 1: Using Excel IF function to Check a Simple Numeric Condition
When using Excel IF function with numbers, you can use a variety of operators to check a condition. Here is a list of operators you can use:
Below is a simple example where students marks are checked. If the marks are more than or equal to 35, the function returns Pass, else it returns Fail.
Example 2: Using Nested IF to Check a Sequence of Conditions
Excel IF Function can take up to 64 conditions at once. While it’s inadvisable to create long Nested IF functions, in the case of limited conditions, you can create a formula that checks conditions in a sequence. In the example below, we check for two conditions.
The first condition checks whether the marks are less than 35. If this is TRUE it returns Fail. In case the first condition is FALSE, which means that the student scored above or equal to 35, it checks for another condition. It checks if the marks are greater than or equal to 75. If this is true, it returns Distinction, else it simply returns Pass.
Example 3: Calculating Commissions Using Excel IF Function
Excel If Function allows you to perform calculations in the value section. A good example of this is calculating the sales commission for sales rep using the IF function. In the example below, a sales rep gets no commission if the sales are less than 50K, gets a 2% commission if the sales are between 50-100K and 4% commission if the sales are more than 100K. Here is the formula used: =IF(B2<50,0,If(B2<100,B22%,B24%))
In the formula used in the example above, the calculation is done within the IF function itself. When the sales value is between 50-100K, it returns B2*2%, which is the 2% commission based on the sales value.
Example 4: Using Logical Operators (AND/OR) in Excel IF Function
You can use logical operators (AND/OR) within the IF function to test multiple conditions at once. For example, suppose you’ve to select students for the scholarship based on marks and attendance. In the example shown below, a student is eligible only if he scores more than 80 and has the attendance of more than 80%.
You can use the AND function within the IF function to first check whether both these conditions are met or not. If the conditions are met, the function returns Eligible, else it returns Not Eligible. Here is the formula that will do this: =IF(AND(B2>80,C2>80%),”Yes”,”No”)
Example 5: Converting all Errors to Zero using Excel IF function
Excel IF function can also be used to get rid of cells that contain errors. You can convert the error values to blanks or zeros or any other value. Here is the formula that will do it: =IF(ISERROR(A1),0,A1) The formula returns a 0 when there is an error value, else it returns the value in the cell. NOTE: If you’re using Excel 2007 or versions after it, you can also use the IFERROR function to do this. Similarly, you can also handle blank cells. In case of blank cells, use the ISBLANK function as shown below: =IF(ISBLANK(A1),0,A1)
Excel IF Function – Video Tutorial
Related Excel Functions:
Excel AND Function. Excel OR Function. Excel NOT Function. Excel IFS Function. Excel IFERROR Function. Excel FALSE Function. Excel TRUE Function
Other Excel tutorials you may find useful:
Avoid Nested IF Function in Excel by using VLOOKUP
800000 0.05% 900000 0.09% 1000000 0.12% 1200000 0.14% 1400000 0.15% 1600000 0.17% 1800000 0.18% 2000000 0.19% 2200000 0.21% 2400000 0.23% 2600000 0.24% 2800000 0.25% 3000000 0.27% please suggest me. I couldn’t find the text (color) using “if formula” from the named range. How can i get it. Please guide me. Many thanks Manish Nepal
