Excel: If Then Insert Function - Here's How
Related Videos: Excel & Business Math 39: Create Excel Invoices, Data Validation Drop-down, VLOOKUP & IF Functions (May 2024).
In Excel, the if then function - or if called formula - is one of the most helpful features. In this article we explain how you can use them optimally.
If then function in Excel
Not only is the formula useful, it can be complicated, especially for inexperienced Excel users who only use it occasionally.
- The function consists of three parts: IF, THEN, ELSE
- This checks a condition and draws conclusions.
- IF stands for the condition that is being evaluated. Excel checks whether a certain behavior applies or not.
- THEN denotes the first consequence. If a condition is defined that has been defined with the formula, THEN a specific case occurs.
- If this THEN consequence does not materialize, the result is another consequence: This is defined with ELSE.
- Note: Often the mathematical characters =, > and <are used. The main definitions are: = stands for equals. stands for unequal. <stands for larger, stands for smaller, > = for smaller or equal.
Structure of the formula
In itself, the structure is quite simple.
- In general, the structure for the formula looks like this: = IF (condition; THEN; OTHER)
- Explained in words: = IF ("This condition is fulfilled;" Do this ";" Otherwise do it ").
- A simplified example of this would be: = IF (2 = 2; "correct"; "wrong")
- The function is initiated with the = sign and the word IF. Excel knows what the command is and can check a corresponding condition.
- This condition is inserted right after the bracket. In the example, this is 2 = 2.
- After the first; sign, the first consequence is defined. You define this sequence. If the condition applies, THEN it is correct.
- If the first result does not arrive, there is a different consequence, defined by "OTHER", which you specified after the second; sign in the formula.
- You can complete the function with a closed bracket.
Detailed example for the Excel function simply explained
This type of task is likely to be familiar to many from school or later in everyday work.
- Let's say an online retailer gives his customers a 5 euro discount if they have paid their invoice after 14 days at the latest.
- Column A lists all customers, B, when they paid and C, whether they received the discount from the retailer or not. This is where the IF formula comes into play.
- Select the appropriate cell in which the IF THEN function should be used. In our example, this is initially cell C2. Here you insert the formula that should check the condition whether the customer paid after 14 days and whether he gets the discount: = IF (B2 <= 14; 5; 0)
- IF defines the condition: Has the customer paid after 14 days at the latest? This is due to the following equation: B2 <= 14 - explained in words, does this mean: Is the numerical value in cell B2 less than or equal to 14?
- The first value after the; sign, in our example 5, indicates the first consequence, the THEN part of the formula. IF the buyer has paid after 14 days at the latest, he will get a 5 euro discount. If he has not done this, he receives nothing, that is 0. This is the ELSE consequence. This is determined by the value after the second; character in the formula.
- If you want to extend the formula to the remaining fields in the column, you can simply drag the green rectangle with which you select a cell with the mouse in the lower right corner. Excel then applies the formula to the other values in the column accordingly, making B2, B3, etc. to determine the correct consequence for the corresponding column.
- Our example shows that the customers Müller and Schmidt receive a discount of 5 euros because they paid their bills after 14 and 7 days, respectively. The THEN case from the formula has arrived here. It took Meier and Huber longer to pay. Accordingly, they do not get a discount. Here is the ELSE case.
If then function in Excel
We remember: The if-then function is made up of three parts - a function then looks like this: = IF ("This condition is fulfilled;" Do this ";" Otherwise do it ").
- To be able to use this function with texts, you simply have to put them in quotation marks.
- In the screenshot you can see an exemplary if-then function. It checks whether there is an "A" in the left field. If this is the case, Excel writes in the right field "Answer A". If there is something else in the field, Excel writes "Answer B".
