Excel if function is a logical function of MS excel which is used to fetch one of the two values based on a condition you specify. If the specified condition is met, it will return one value, otherwise it will return another value. If functions are one of the widely used and versatile formulas of MS excel and other spreadsheet programs.
Although Excel if function looks simple and straightforward, it is quite powerful and can save a lot of your time if used properly at the right place. In this article, I will start from the basics and go to the advanced level with nested if function, if formula in arrays, if function with ‘AND’ and ‘OR” and if function combined with vlookup. We will discuss it with real-life examples which will help you to understand it better.
Let’s start with a simple example. Say you have a list of your customers with gender and you want to give away the holiday gifts to all of them. You have separate gifts for men and women – pen sets for men and purse for ladies. If you use If function, in no time you can get the number of pen sets and ladies purse required. Here, If function will look at a simple condition “Is the person male”? If the answer is “TRUE”, it will return pen set otherwise purse.
Excel If function Syntax
IF(logical_test, [value_if_true], [value_if_false])
To make your life simple in understanding this syntax, the easier way to interpret it is as follows:
IF(Check something, If true do this, Otherwise do that)
For our example, it would be:
IF(Check if the person is male, Return pen set, Return purse)
Explanation of If function formula arguments
- logical_test: An expression or value which is tested to see whether it is true or false. This field is required. Suppose we want to determine if the given number in cell A2 is negative, the expression will be A2<0. The syntax for determining whether the value is positive or negative should look like this:
You can use any of the following comparison operators in the expression for logical_test argument:
|= (equal to)||A1=B1|
|> (greater than)||A1>B1|
|< (less than)||A1<B1|
|>= (greater than or equal to)||A1>=B1|
|<= (less than or equal to)||A1<=B1|
|<> (not equal to)||A1<>B1|
- value_if_true: The value which you want to display, if the logical_test is true. This field is optional. In this example, if the cell value is -5 and we want to display word ‘negative’, we need to put “Negative” in this argument value. Note here that the text is enclosed in colons. This is required for strings used in excel formulae. Since this argument is optional, you can omit it by putting just a comma after logical_test argument like this:
In this case, if the logical_test is true, if function will return 0 (zero).
- Value_if_false: The value which you want to display, if the logical_test is False. Similar to value_if_true argument, this field is optional. Here also, you can omit the argument by putting just a comma after value_if_true argument. As you may guess, if function will return a 0 (zero) in that case.
How to use Excel If function
This paragraph is for starters who are not comfortable with using excel functions. If you know how to use basic functions like SUM, AVERAGE, etc.; you can skip it.
Suppose, as a shop owner, we are interested to give holiday discounts based on the total order value. If the value is less than $100, the discount is 8%. For $100 and above, the discount is 12%. Based on the value entered in a cell, we will use IF function to determine the discount percentage.
- Open a new excel workbook.
- In cell B4, we will enter the total order value. Let’s enter 80 here at this time.
- We want to display the percentage discount in cell C4. Click on cell C4 to select it.
- Click on “insert item” icon just before the formula bar above worksheet to open the function dialog box.
- You can also find this “insert item” icon in the Formulas tab.
- Select the category “logical”.
- Select “IF” from list of available functions and click OK.
- The function argument dialog box will appear which will ask you to enter the values for the three arguments i.e. logical_test, value_if_true and value_if_false.
- Select the logical_test text box and click on cell B4, then type < (less than sign) followed by 100.
- In the value_if_true argument, enter 8.
- In the value_if_false argument, enter 12.
- Click on OK.
- Since in this case 80 is less than 100, 8 should appear in cell C4.
- Change the value in cell B4 to 110, the value in cell C4 will change to 12.
- If you wish to see the IF formula used, click on cell C4 and see the formula bar. You will find the following formula here: =IF(B4<100,8,12)
Example of omitted argument
Let’s take the same example as above. The only change we are considering here is that if the total order value is less than $100, there will be no discount. So, for the order value less than $100, 0 should appear in the cell C4.
To achieve this functionality, we just need to omit the value_if_true argument. Now, the formula will change as follows:
Try putting 70 in the B4 cell, the value displayed in C4 cell will be 0 (Zero). If you don’t want 0 to appear in the cell; put “” (empty text) in the value_if_true argument like this.
Nested If Functions
Now, as you have fully understood the basics, let’s move on to some advanced topics. Simple IF function is good if you have only one logical test to perform. However, where multiple logical tests are required, we can use nested IF function.
To understand nested IF functions, let’s create an income tax calculator. Suppose, income tax is calculated based on the total income as per following table:
|Total Income||Income Tax (Percentage of Income)|
|Less than Rs. 300000||5|
|Rs. 300000 – Rs. 600000||10|
|More than Rs. 600000||20|
So, how do you determine the income tax percentage? If you think like me, you will first determine if the total income is less than Rs. 300000. If yes, the income tax percentage is 5; otherwise again you see if the total income is less than Rs. 600000. If yes, the income tax percentage is 10; otherwise it is 20, Right?
Great..! Nested IF function works in the similar fashion.
We will use the same worksheet, enter our total income in cell B4 and get the income tax percentage value in cell C4.
First let’s test, if total income is less than Rs. 300000 or not? If True, return 5; otherwise return “Yet to determine”. In cell C4, type the following formula (or you can use function dialog box).
=IF(B4<300000,5,”Yet to determine “)
Now, in place of “Yet to determine”, we will place another IF function to test if the income is less than Rs. 600000? If True, return 10; otherwise return 20.
So, we replace “Yet to determine” with the following formula: IF(B4<600000,10,20)
Our final formula for calculating income tax is a nested IF functions as follows:
Perfect. Now, test it with a few values to see what percentage it returns.
Syntax for nested IF function
IF(logical_test1, [value_if_true1], IF( logical_test2, [value_if_true2], [value_if_false2]))
You can nest up to 64 IF functions as value_if_true and value_if_false arguments as per your requirement.
Let’s increase the tax slab in our example and use multiple Nested IF functions.
|Total Income||Income Tax (Percentage of Income)|
|Less than Rs. 300000||5|
|Rs. 300000 – Rs. 600000||10|
|Rs. 600000 – Rs. 1000000||20|
|More than Rs. 1000000||30|
This time, we will straightway determine the income tax value (not percentage). Try yourself fist and then match with the following formula.
=IF(B4<300000, B4*0.05, IF(B4<600000, B4*0.1, IF(B4<1000000,B4*0.2,B4*0.3)))
IF function with Boolean (AND/OR) function
Combining excel Boolean functions like ‘AND’ and ‘OR’ makes IF function more powerful. Suppose you need to check more than 2 conditions to see if both are true, you can very well use nested IF function. But a better way to do this is to use IF function combined with ‘AND’ function.
How does AND function work?
AND function will return TRUE only if all the parameters are true.
=AND(10<20, 20<30, 30<50) will return TRUE
=AND(10>20, 20<30, 30<50) will return FALSE
IF Function combined with AND Function
In the previous example, let’s assume that for senior citizens (above 60 years old) with total income less than Rs. 500000, there will be no income tax; otherwise it will be 15% flat.
We will enter the total income in cell B4 and age of the person in cell B5 and get the percentage in cell C4. So, the formula in cell C4 is:
=IF(AND(B4<500000, B5>60), 0, 15)
Simple and straightforward, isn’t it?
IF Function combined with OR Function
OR function is equally powerful when used with IF function. As you can understand; OR function will return TRUE if any one or more of the parameters are true.
So, the following formula
=OR(10>20, 20<30, 30<50) will return TRUE
Suppose, in a bus reservation, there is a discount of 20% for kids (less than 10 years) and senior citizens (above 60 years); the formula can be:
=IF(OR(B4<10, B4>50), 20, 0)
The OR function in formula will look into the cell B4 to determine if it less than 10 OR greater than 60. If any of the two is true, it will return TRUE. IF function in turn, will return 20. Pretty simple!
IF function in array
If you pass an array in any of the arguments in IF function, each element of the array is evaluated while executing the formula.
There is a group of functions COUNTIF, AVERAGEIF and SUMIF where the function first checks the array or range that meets the specific criteria and then do calculations like count, average or sum.
Suppose we want to get the average number of sales for 10 days excluding holidays where the sales number is 0. Here, we will use AVERAGEIF function.
The syntax for this group of functions is as follows:
range: Required field. One or more cells to count, average or sum, including numbers or names, arrays, or references that contain numbers. Blank and text values are not taken into account.
criteria: Required field. A number, expression, cell reference, or text string that defines which cells to be taken into account. For example, criteria can be expressed as 20, “>20″, B4, “books”, etc.
VLOOKUP and IF Function
As you know now, for testing multiple conditions, we can use nested IF functions. However, if the number of conditions is huge, we should better use VLOOKUP function. VLOOKUP is much more powerful and can be very handy in complex conditions. Primary and basic function of VLOOKUP is to search any value from a table with data and return a value from a different column from the same row. The VLOOKUP function is a little complex and I have a written a separate article on excel VLOOKUP.
You may like to go through my Excel VLOOKUP Tutorial.
I hope you find this tutorial on Excel IF function useful. If you have any question, suggestion or feedback, please feel free to write in the comments section below.