Excel IF Function | Formula Examples FREE Video
| Formula Examples + FREE Video
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.
- 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.
– 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
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
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:
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:
Example 5: Converting all Errors to Zero using 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:
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:
– Video Tutorial
Excel IF Function
The IF function runs a logical test and returns one value for a TRUE result, and another for a FALSE result. For example, to «pass» scores above 70: =IF(A1>70,»Pass»,»Fail»). More than one condition can be tested by nesting IF functions. The IF function can be combined with logical functions like AND and OR to extend the logical test.
- logical_test — A value or logical expression that can be evaluated as TRUE or FALSE.
- value_if_true — [optional] The value to return when logical_test evaluates to TRUE.
- value_if_false — [optional] The value to return when logical_test evaluates to FALSE.
The IF function is used to run a logical test, and reacts differently depending on whether the result is TRUE or FALSE. The first argument, logical_test, is an expression that returns either TRUE or FALSE. Both value_if_true and value_if_false are optional, but at least one of them must be provided. The result from IF can be a value, a cell reference, or even another formula.
In the example shown above, we want to assign either «Pass» or «Fail» based on a test score. A passing score is 70 or higher. The formula in D6, copied down, is:
Translation: If the value in C6 is greater than or equal to 70, return «Pass». Otherwise, return «Fail».
The logical flow of this formula can be reversed. The formula below returns the same result:
Translation: If the value in C6 is less than 70, return «Fail». Otherwise, return «Pass».
Both formulas above, when copied down, will return correct results.
Note: If you are new to the idea of formula criteria, this article explains many examples.
Another formula
The IF function can return another formula as a result. For example, the formula below will return A1*5% when A1 is less than 100, and A1*7% when A1 is greater than or equal to 100:
Nested IF statements
The IF function can be «nested». A «nested IF» refers to a formula where at least one IF function is nested inside another in order to test for more conditions and return more possible results. Each IF statement needs to be carefully «nested» inside another so that the logic is correct.
For example, the following formula can be used to assign a grade rather than a pass / fail result:
Up to 64 IF functions can be nested. However, in general, you should consider other functions, like VLOOKUP or HLOOKUP for more complex scenarios, because they can handle more conditions in a much more streamlined fashion.
Logical operators
When you are constructing a test with IF, you can use any of the following logical operators:
Comparison operator Meaning Example = equal to A1=D1 > greater than A1>D1 >= greater than or equal to A1>=D1 not equal to A1<>D1 The IF function doesn’t support wildcards, but you can combine IF with COUNTIF to get basic wildcard functionality.
IF with AND, OR, NOT
The IF function can be combined with the AND function and the OR function. For example, to return «OK» when A1 is between 7 and 10, you can use a formula like this:
Translation: if A1 is greater than 7 and less than 10, return «OK». Otherwise, return nothing («»).
To return B1+10 when A1 is «red» or «blue» you can use the OR function like this:
Translation: if A1 is red or blue, return B1+10, otherwise return B1.
Translation: if A1 is NOT red, return B1+10, otherwise return B1.
How to Use the IF-THEN Function in Excel
Inputting the IF-THEN function in Excel helps with data-based decisions
- Tweet
- Share
- Excel
- Word
- Powerpoint
- Outlook
What to Know
- The syntax of IF-THEN is =IF(logic test,value if true,value if false).
- The first argument tells the function what to do if the comparison is true.
- The second argument tells the function what to do if the comparison is false.
This article explains how to use the IF-THEN function in Excel for Microsoft 365, Excel 2019, 2016, 2013, 2010; Excel for Mac, and Excel Online, as well as a few examples.
Inputting IF-THEN in Excel
The IF-THEN function in Excel is a powerful way to add decision making to your spreadsheets. It tests a condition to see if it’s true or false and then carries out a specific set of instructions based on the results.
For example, by inputting an IF-THEN in Excel, you can test if a specific cell is greater than 900. If it is, you can make the formula return the text «PERFECT.» If it isn’t, you can make the formula return «TOO SMALL.»
There are many conditions you can enter into the IF-THEN formula.
The IF-THEN function’s syntax includes the name of the function and the function arguments inside of the parenthesis.
This is the proper syntax of the IF-THEN function:
=IF(logic test,value if true,value if false)
The IF part of the function is the logic test. This is where you use comparison operators to compare two values.
The THEN part of the function comes after the first comma and includes two arguments separated by a comma.
- The first argument tells the function what to do if the comparison is true.
- The second argument tells the function what to do if the comparison is false.
A Simple IF-THEN Function Example
Before moving on to more complex calculations, let's look at a straightforward example of an IF-THEN statement.
Our spreadsheet is set up with cell B2 as $100. We can input the following formula into C2 to indicate whether the value is larger than $1000.
This function has the following arguments:
- B2>1000 tests whether the value in cell B2 is larger than 1000.
- "PERFECT" returns the word PERFECT in cell C2 if B2 is larger than 1000.
- "TOO SMALL" returns the phrase TOO SMALL in cell C2 if B2 is not larger than 1000.
The comparison part of the function can compare only two values. Either of those two values can be:
- Fixed number
- A string of characters (text value)
- Date or time
- Functions that return any of the values above
- A reference to any other cell in the spreadsheet containing any of the above values
The TRUE or FALSE part of the function can also return any of the above. This means that you can make the IF-THEN function very advanced by embedding additional calculations or functions inside of it (see below).
When inputting true or false conditions of an IF-THEN statement in Excel, you need to use quotation marks around any text you want to return, unless you're using TRUE and FALSE, which Excel automatically recognizes. Other values and formulas don't require quotation marks.
Inputting Calculations Into the IF-THEN Function
You can embed different calculations for the IF-THEN function to perform, depending on the comparison results.
In this example, one calculation is used to calculate the tax owed, depending on the total income in B2.
The logic test compares total income in B2 to see if it's greater than $50,000.00.
In this example, B2 is not larger than 50,000, so the "value_if_false" condition will calculate and return that result.
In this case, that's B2*0.10, which is 4000.
The result is placed into cell C2, where the IF-THEN function is inserted, will be 4000.
You can also embed calculations into the comparison side of the function.
For example, if you want to estimate that taxable income will only be 80% of total income, you could change the above IF-THEN function to the following.
This will perform the calculation on B2 before comparing it to 50,000.
Never enter a comma when entering numbers in the thousands. This is because Excel interprets a comma as the end of an argument inside of a function.
Nesting Functions Inside of an IF-THEN Function
You can also embed (or "nest") a function inside of an IF-THEN function.
This lets you perform advanced calculations and then compare the actual results to the expected results.
In this example, let's say you have a spreadsheet with five students' grades in column B. You could average those grades using the AVERAGE function. Depending on the class average results, you could have cell C2 return either "Excellent!" or "Needs Work."
This is how you would input that IF-THEN function:
This function returns the text "Excellent!" in cell C2 if the class average is over 85. Otherwise, it returns "Needs Work."
As you can see, inputting the IF-THEN function in Excel with embedded calculations or functions allows you to create dynamic and highly functional spreadsheets.
IF Function
The Excel IF Statement tests a given condition and returns one value for a TRUE result and another value for a FALSE result. For example, if sales total more than $5,000, then return a “Yes” for Bonus – Otherwise, return a “No” for Bonus. We can also use the IF function to evaluate a single function, or we can include several IF functions in one formula. Multiple IF statements in Excel are known as nested IF statements.
As a financial analyst Financial Analyst Job Description The financial analyst job description below gives a typical example of all the skills, education, and experience required to be hired for an analyst job at a bank, institution, or corporation. Perform financial forecasting, reporting, and operational metrics tracking, analyze financial data, create financial models , the IF function is used often to evaluate and analyze data by evaluating specific conditions.
The function can be used to evaluate text, values, and even errors. It is not limited to only checking if one thing is equal to another and returning a single result. We can also use mathematical operators and perform additional calculations, depending on our criteria. We can also nest multiple IF functions together to perform multiple comparisons.
IF Formula
=IF(logical_test, value_if_true, value_if_false)
The function uses the following arguments:
- Logical_test (required argument) – This is the condition to be tested and evaluated as either TRUE or FALSE.
- Value_if_true (optional argument) – The value that will be returned if the logical_test evaluates to TRUE.
- Value_if_false (optional argument) – The value that will be returned if the logical_test evaluates to FALSE.
When using the IF function to construct a test, we can use the following logical operators:
- = (equal to)
- > (greater than)
- >= (greater than or equal to)
- < (less than)
- <= (less than or equal to)
- <> (not equal to)
How to use the Excel IF Function
To understand the uses of the Excel IF statement function, let’s consider a few examples:
Example 1 – Simple Excel IF Statement
Suppose we wish to do a very simple test. We want to test if the value in cell C2 is greater than or equal to the value in cell D2. If the argument is true, then we want to return some text stating “Yes it is”, and if it’s not true, then we want to display “No it isn’t”.
You can see exactly how the Excel IF statement works in the simple example below.
Result when true:
Result when false:
Example 2 – Excel IF Statement
Suppose we wish to test a cell and ensure that an action is taken if the cell is not blank. We are given the data below:
In the worksheet above, we listed AGM-related tasks in Column A. Remarks contain the date of completion. In Column B, we will use a formula to check if the cells in Column C are empty or not. If a cell is blank, the formula will assign the status “open.” However, if a cell contains a date, then the formula will assign a status of “closed.” The formula used is:
We get the results below:
Example 3 – Excel IF Statement
Sellers frequently provide a discount based on the quantity purchased. Suppose we are given the following data:
Using multiple IF functions, we can create a formula to check multiple conditions and perform different calculations depending on what amount range the specified quantity falls in. To calculate the total price for 100 items, the formula will be:
We get the result below:
Things to remember about the IF Function
- The Excel IF function will work if the logical_test returns a numeric value. In such a case, any non-zero value is treated as TRUE and zero is treated as FALSE.
- #VALUE! error – Occurs when the given logical_test argument cannot be evaluated as TRUE or FALSE.
- When any of the arguments are provided to the function as arrays, the IF function will evaluate every element of the array.
- If we wish to count conditions, we should use the COUNTIF and COUNTIFS functions.
- If we wish to add up conditions, we should use the SUMIF and SUMIFS functions.
Reasons to use an Excel IF Statement
There are many reasons why an analyst or anyone who uses Excel would want to build IF formulas.
Common examples include:
- To test if an argument is true or false
- To output a NUMBER
- To output some TEXT
- To generate a conditional formula (e.g., the result is C3+B4 if true and N9-E5 if false)
- To create scenarios Scenario Analysis Scenario analysis is a process of examining and evaluating possible events or scenarios that could take place in the future and predicting the to be used in financial modeling What is Financial Modeling Financial modeling is performed in Excel to forecast a company’s financial performance. Overview of what is financial modeling, how & why to build a model.
- To calculate a debt schedule Debt Schedule A debt schedule lays out all of the debt a business has in a schedule based on its maturity and interest rate. In financial modeling, interest expense flows or a fixed asset depreciation schedule Depreciation Schedule A depreciation schedule is required in financial modeling to link the three financial statements (income, balance sheet, cash flow) in Excel. in accounting
Additional resources
Thanks for reading CFI’s guide on how to use an Excel IF Statement. By taking the time to learn and master these functions, you’ll significantly speed up your financial analysis. To learn more, check out these additional CFI resources:
Free Excel Tutorial
To master the art of Excel, check out CFI’s FREE Excel Crash Course, which teaches you how to become an Excel power user. Learn the most important formulas, functions, and shortcuts to become confident in your financial analysis.
:max_bytes(150000):strip_icc()/entering-data-with-if-function-3123603-2-5bf19dd646e0fb00267d033b.jpg)
:max_bytes(150000):strip_icc()/entering-data-with-if-function-3123603-3-5bf1a17bc9e77c00510b85cf.jpg)
