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 analyst, 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(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)
Step #1 – Understand the Syntax of the Excel IF Function
Although there aren’t any checkboxes in Excel’s IF function, it uses the same logic as the restaurant example presented above. Here is the syntax for the IF function:
Like every other function, the Excel IF function starts with an equals sign (=), followed by the function name and a series of arguments inside parentheses.
The Excel IF function accepts 3 arguments:
- logical_test – think of the logical test as a statement that you make about your data. Something like “the customer has requested delivery”.
- value_if_true – do this if logical_test is true. In the restaurant example, value_if_true amounts to “Add $3.00 to the customer’s order.”
- value_if_false – do this if logical_test is false. If the customer hasn’t requested delivery, no additional charge is added to the customer’s order.
Step #3 – Test for Multiple Conditions Using Nested Functions
What if the restaurant offered free delivery for orders over $10.00? We would need to test for multiple conditions by nesting Excel’s AND function inside the IF function. Check out the formula (below).
The AND function has been placed in the value_if_true position of our IF statement. Basically the function says “if C4=’Yes’ AND the cost of the order in cell B4 is less than $10, charge $3 for delivery. Otherwise, don’t add an extra charge”.
Now you know just enough about Excel’s IF function to be dangerous! Have you incorporated the Excel IF function into any of your spreadsheets? Let me know in the comments below!