8
In Excel, the IF function, also known as the IF formula, is one of the most helpful features. It’s easy to explain how to use it effectively.
IF function in Excel: Everything you need to know about this useful formula
The IF formula is not only useful, but can also be complicated, especially for inexperienced Excel users.
- The function consists of three parts: IF, THEN, ELSE. This checks a condition and draws conclusions.
- IF stands for the condition being evaluated. Excel checks whether a specific condition is true or not.
- IF refers to the first consequence. If a condition defined by the Excel formula is true, THEN a specific outcome occurs.
- If this THEN consequence does not apply, this results in a different outcome: This is defined with ELSE.
- Note: The mathematical symbols =, >, and < are frequently used here. This primarily results in the following definitions: = stands for equal. <> stands for not equal. < stands for greater than, <= for greater than or equal to. > stands for less than, >= for less than or equal to.
Structure of the Excel If-Then Formula: Step by Step
The structure itself is quite simple. Like other formulas in Excel, you just need to know the individual components.
- Generally, the structure of the formula looks like this: =IF(Condition;THEN;ELSE). Be sure to always enter the equals sign; otherwise, the formula won’t work.
- Explained in plain language: =IF(“This condition is met;”Do this“;”Otherwise do that“). A simplified example of this would be: =IF(2=2;‘True’;”False”)
- The function is initiated with the = sign and the word IF. This tells Excel what the command is and allows it to check the corresponding condition.
- This condition is inserted immediately after the parentheses. In the example, this is 2=2.
- After the first ; character the first result is defined. You define this result. If the condition is true, THEN the result is “True”.
- If the first result does not apply, a different consequence results, defined by “ELSE,” which you specified after the second semicolon in the formula. You complete the function with a closing parenthesis.
- Note: The function works the same way in every version of Excel . It doesn’t matter whether you’re using the desktop or web version, for example.
Excel IF Function: Detailed Example
Many people are likely familiar with this type of task, either from school or later in their daily work life.
- Let’s assume an online store offers customers a five-euro discountif they pay their bill no later than 14 days after it is due.
- In Column A all customers are listed, in B, when they paid, and in C, whether they have already received the discount or not. This is where the IF formula in Excel comes into play.
- Select the appropriate cell where the IF function is to be used. In our example, this is initially cell C2. Here, enter the formula that checks whether the customer paid within 14 days and whether the discount is applied: =IF(B2<=14,5,0)
- IF defines the condition: Has the customer paid within 14 days at the latest?
This is determined by the following equation: B2<=14—in plain language, this means: Is the numerical value in cell B2 less than or equal to 14?
- The first value after the ; character, in our example the 5, specifies the first consequence, the IF part of the formula. IF the buyer has paid within 14 days at the latest, they receive a five-euro discount. If that hasn’t happened, there is no discount, i.e., 0. This is the ELSE consequence. This is determined by the value after the second semicolon in the formula.
- If you want to apply the formula to the remaining cells in the column, you can simply drag the green rectangle—which you use to select a cell—downward from the bottom-right corner with your mouse. Excel then applies the formula to the other values in the column and uses B2, B3, and so on to determine the correct result for the corresponding column .
- In our example, we see that customers Müller and Schmidt receive a discount of five euros because they paid their bills after 14 and seven days, respectively. The IF-case from the formula has occurred here.
- Meier and Huber both took longer to pay. Accordingly, they do not receive a discount. Here, the ELSE-case has occurred.
Using the If-Then function in Excel with text: Here’s how it works
Let’s recap: The If-Then function consists of three parts—a function looks like this: =IF(“This condition is met;”Do this“;”Otherwise do that”).
- To use this function with text as well, you simply need to enclose it in quotation marks.
- In the screenshot, you can see an example of an IF function. It checks whether the left field contains an “A” . If that is the case, Excel writes “Answer A” in the right-hand field. If something else is in the field, Excel writes “Answer B”.
- This allows you to use the If-Then function effectively in any document. Just don’t forget the quotation marks.
