What is an Excel IF Statement?
An 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 analystFinancial Analyst Job DescriptionSee a real Financial Analyst Job Description including all the skills, experience, and education required to be the successful candidate for the job. 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 formula uses the following arguments:
- Logical_test (required argument) – It is the condition to be tested and evaluated as either TRUE or FALSE.
- Value_if_true (optional argument) – It is the value that will be returned if the logical_test evaluates to TRUE.
- Value_if_false (optional argument) – It is 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 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 us 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 of 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 simple example below.
Result when true:
Result when false:
Download the simple XLS template.
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 cells in Column C are empty or not. If a cell is blank, the formula will assign a status “open.” However, if a cell contains a date then the formula will assign a status as “closed.” The formula used is:
We get the results below:
Example 3 – Excel IF Statement
Generally, sellers provide a discount based on 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 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 is 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 (i.e. the result is C3+B4 if true and N9-E5 if false)
- To create scenariosScenario AnalysisScenario analysis is a technique used to analyze decisions through speculating various possible outcomes in financial investments. In financial modeling, this process is typically used to estimate changes in the value of a business or cash flow. By using the Choose or Offset functions an analyst can crease base case to be used in financial modelingWhat is Financial ModelingFinancial modeling is performed in Excel to forecast a company's financial performance. Overview of what is financial modeling, how & why to build a model. A 3 statement model links income statement, balance sheet, and cash flow statement. More advanced types of financial models are built for valuation, plannnig, and
- To calculate a debt scheduleDebt ScheduleA 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 into the income statement, closing debt balance flows onto the balance sheet, principal repayments flow through the cash flow statement, completing the scheudle or fixed asset depreciation scheduleDepreciation ScheduleA depreciation schedule is required in financial modeling to forecast the value of a company's fixed assets (balance sheet), depreciation expense (income statement) and capital expenditures (cash flow statement). In financial modeling, a depreciation schedule is requried to link the three financial statements in Excel in accounting
Click here to download the sample Excel file
Additional resources
Thanks for reading CFI’s guide on how to make 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 resources:
- Advanced Excel Course
- Advanced Excel Formulas GuideAdvanced Excel Formulas Must KnowThese advanced Excel formulas are critical to know and will take your financial analysis skills to the next level. Advanced Excel functions you must know. Learn the top 10 Excel formulas every world-class financial analyst uses on a regular basis. These skills will improve your spreadsheet work in any career
- Excel Shortcuts ListExcel Shortcuts PC MacExcel Shortcuts - List of the most important & common MS Excel shortcuts for PC & Mac users, finance, accounting professions. Keyboard shortcuts speed up your modeling skills and save time. Learn editing, formatting, navigation, ribbon, paste special, data manipulation, formula and cell editing, and other shortucts
- Financial Modeling ProgramFMVA™ CertificationThe Financial Modeling & Valueation Analyst (FMVA)™ accreditation is a global standard for financial analysts that covers finance, accounting, financial modeling, valuation, budgeting, forecasting, presentations, and strategy.