How to use IF function in Excel
How to use IF function in Excel
This is a step-by-step guide on how to use IF function in Excel. It shows you how to create a formula using the IF function, it includes several IF formula examples
The Excel IF function performs a logical test and returns one value when the condition is TRUE and another when the condition is FALSE.
How do you write an if-then formula in Excel? Well, the syntax for IF statements is the same in all Excel versions. This means that you can use any of the examples shown in this article in Excel for Microsoft 365 or Excel 2021, 2019, 2016, 2013, 2010, 2007, and 2003.
How to use IF function in Excel:
Select the cell where you want to insert the IF formula. Using your mouse or keyboard, navigate to the cell where you want to insert your formula.
Type =IF(
Insert the condition that you want to check, followed by a comma (,). The first argument of the IF function is the logical_test. This is the condition that you want to validate. For example C6 > 70.
Insert the value to display when the condition is TRUE, followed by a comma (,). The second argument of the IF function is value_if_true. Here, you can insert a nested formula or a simple message such as “YES”.
Insert the value to display when the condition is FALSE. The last argument of the IF function is value_if_false. Just like the previous step, you can insert a nested formula or display a message such as “NO”. This can also be set as an empty string (“”), which will display a cell that looks blank.
Type ) to close the function and press ENTER
The following video shows you exactly how to apply the six steps described above and create your first IF formula.
The syntax that shows how to create an IF function in Excel is explained below:
=IF(logical_test, [value_if_true], [value_if_false])IF is a logical function and implies setting 3 arguments:
logical_test – The logical condition that you want to test. This will return either a TRUE or a FALSE value.
value_if_true – [optional] The value or formula which will be used when logical_test is TRUE.
value_if_false – [optional] The value or formula which will be used when logical_test is FALSE.
Please remember that while both value_if_true and value_if_false are optional, at least one of them needs to be supplied. Otherwise, your IF formula will simply return 0 (zero).
Where is the IF function in Excel? Since this is a logical function, you can find the IF function in the Formulas tab, Function Library section, under Logical.
Logical operators for IF function
The IF function is one of the most used Excel functions, and it allows you to return different values when the logical condition supplied is TRUE or FALSE. An Excel if-then formula can use the following logical operators:
= equal to
<> not equal to
> greater than
>= greater than or equal to
< lower than
<= lower than or equal to
The IF function doesn’t support wildcards.
Your first IF formula
The IF function runs a logical test and returns different values depending on whether the result is TRUE or FALSE. The result from IF can be a value, a cell reference, or even another formula.
Now let’s move on to some examples.
We’ll be evaluating exam grades. If the student obtained a score higher than or equal to 70, then we will return the message “Pass.” If the grade is lower than 70, then we will display “Fail.”
In this example, I have inserted the following formula in cell F9:
=IF(E9>=70, "Pass", "Fail")The 3 arguments for this IF formula are:
logical_test: E9>=70
value_if_true: Pass is returned if E9>=70.
value_if_false: Fail is returned if E9<70.
Please note that when you want to use text in your IF formulas (like a word or sentence), you need to wrap the text in quotes (e.g. “Fail”). The only exception is while using TRUE or FALSE, which are built-in functionalities that Excel recognizes automatically.
How to use the IF function in Excel with another function or formula
The beauty of the IF function is that it allows us to build complex financial models with lots of interdependencies. This includes using different formulas based on conditional logic.
In our next example, we will use the IF function to calculate a payment fee based on the value of the order. If the order value is higher than or equal to $1000, then it should calculate a payment fee of 1.00%. However, if the total order value is lower than $1000, then it should use 1.50%.
The formula in cell F31 is:
=IF(E31>=1000, E31*1%, E31*1.5%)
Now let’s look at an IF formula that is dependent on user input. If we select free shipping for the order, then the shipping fee will be set to zero. Otherwise, it will be calculated as 3% of the order value.
This is something really easy to achieve, but it will open up so many opportunities for you to use the IF function in the future.
How to use nested IF statements in Excel
Nesting more IF functions allows you to perform multiple comparisons and create more complex formulas. However, you can only nest up to 64 IF functions in Excel. If you ever reach this limit (I never did), I can guarantee that there is a better and more elegant solution using functions like VLOOKUP, SUMIF, or COUNTIFS.
In the next example, I wrote a formula with several nested IF functions to assign a grade to a list of students based on their test results.
=IF(E71<60, "F", IF(E71<70, "D", IF(E71<80, "C", IF(E71<90, "B", "A"))))The order of the conditions is important. When the conditions overlap, Excel will retrieve the [value_if_true] argument from the first IF statement that returns TRUE. This is why the conditions from the formula above need to be inserted in the same order for the formula to work properly.
Note: If you are running Office 365, then you can also look at the new IFS function. This function runs multiple tests and returns the value corresponding to the first TRUE result. It’s a very useful alternative to nested IF formulas and makes your formulas much easier to understand by others. You can read more about IFS on Microsoft’s website.
How to use IF formula with OR function in Excel
OR allows you to supply alternative conditions to an IF statement. This opens up opportunities to create complex scenarios where certain behavior is triggered by multiple possible conditions.
Let’s look at an IF formula that calculates a 2.00% shipping fee when the total order value is higher than $1000 or when there are more than 5 items in the order.
The IF OR statement I’ve used in cell H106 is:
=IF(OR(G106>1000, F106>5), G106*2%, 0)
The OR function evaluates if G106>1000 or if F106>5 and the formula returns TRUE when either or both conditions are fulfilled.
How to use IF formula with AND function in Excel
AND allows you to supply multiple criteria to an IF statement. Basically, the IF function returns TRUE if, and only if, all the conditions are met.
Working with our previous example, let’s apply the shipping fee only when the total order value is higher than $1000 and the order contains more than 5 items.
The IF AND statement I’ve used in cell H106 is:
=IF(AND(G128>1000, F128>5), G128*2%, 0)
The AND function evaluates if G106>1000 and if F106>5 and returns TRUE when both conditions are fulfilled.
How to use IF function with VLOOKUP in Excel
VLOOKUP can be nested inside an IF formula to retrieve data when a condition is TRUE or FALSE. In the next example, I will show you how to calculate shipping fees based on a table containing the thresholds and percentages to be applied depending on the order value.
The formula I’ve used in cell F152:
=IF(G152="No", VLOOKUP(E152, $J$146:$K$152, 2, TRUE)*E152, 0)
The formula uses the following arguments:
logical_test: G152="No"
value_if_true: VLOOKUP(E152, $J$146:$K$152, 2, TRUE)*E152 is used to retrieve the corresponding shipping fee percentage when G152=”No”
value_if_false: 0 is returned if G152 is anything else than “No.” In our case, the alternative is selecting “Yes” from the drop-down list.
Note: Remember that I’ve used a VLOOKUP formula with an approximate match argument. This means that your data must be sorted in ascending order by lookup value (in our case, the Order amount).
Let me know if you have questions on how to use IF function in Excel or if you need advice on how to nest multiple IF statements in your Excel project by leaving a comment below.
Originally published at https://excelexplained.com