With the IF function in Excel you have a formula for calculating logical comparisons between current and expected values. Two different results can be displayed depending on the conditions previously set. Either the comparison is TRUE or FALSE. The function is therefore well suited as an intermediate step for checking content in order to avoid undesired errors. We will show you how the function is structured and explain how it works using examples.
Read more about more tips about controls and formulas in Excel here.
Structure of the function
Depending on whether the checked condition is met or not, a correspondingly different value also comes out. The notation of the formula in Excel (also called syntax) is composed as follows:
WENN(Prüfung;Dann_Wert;[Sonst_Wert])
- If the CONDITION is met (true), THEN (value)
- If the CONDITION is NOT fulfilled (false), ELSE (value)
The arguments of the function are listed individually:
| Argument name | description | example |
| test | The condition to be checked. | The traffic light is red. |
| Then_value | The value to be returned when the condition is FULFILLED. | Stand |
| Else_value | The value to be returned if the condition was NOT FULFILLED. | Go |
| Argumentname | Beschreibung | Beispiel |
| Prüfung | Die Bedignung, die geprüft werden soll. | Die Ampel ist rot. |
| Dann_Wert | Der Wert, der zurückgegeben werden soll, wenn die Bedingung ERFÜLLT wurde. | Stehen |
| Sonst_Wert | Der Wert, der zurückgegeben werden soll, wenn die Bedingung NICHT ERFÜLLT wurde. | Gehen |
Attention: Make sure to separate the individual values of the formula with a semicolon..
If we take the example from the overview table, the formula should be as follows:
=WENN(B3="Rot";"Stehen";"Gehen")
The condition to be checked here is the " traffic light color ". The value returned is the action resulting from the comparison. If " red " is entered in cell B3 , " standing " appears in cell C3 , if " other value " is entered, " walking " appears.
If you copy the formula into the line below, Excel automatically checks cell B4 . Since the condition ( B4 = "Red" ) was not met, the " Else_Value " is displayed.
Note: You can use these functions in Excel to evaluate numerical values as well as texts (as you can see in the example above). When marking text content, make sure to use quotation marks ..
Create the "IF" function in Excel
To perform a calculation using the IF function, simply follow our step-by-step instructions .
1st step:
Open Excel and switch to the " Formulas " tab . Now click on the question mark symbol , which is labeled " Logical ". Select " IF " in the drop-down menu .
2nd step:
The " Function arguments " window then opens . Enter the test , the then_value and the otherwise_value here . Confirm your entries with " OK ".
The IF function in examples
"IF you have earned more than € 1,000, THEN you can spend money. IF you have earned less than € 1,000, THEN you have to save." The corresponding formula should look like this in Excel:
=WENN(B3>1000;"Ausgeben"; "Sparen")
You can also express or expand the results using formulas, as in the following example. "IF an employee receives a bonus, THEN this bonus should be 2% of his salary. IF an employee does not receive a bonus, 0 should be entered". In Excel this calculation would have to be formulated as follows:
=WENN(C3="Ja";2%*B3;0)
Nested IF function
Instead of a single test, you can set several conditions at the same time and nest them in one another. Nesting in this case means that several IF functions are queried one after the other. For this purpose, the second IF function is inserted in place of the else_value. To illustrate this, we extend the first example shown :
=WENN(B3="rot";"stehen";WENN(B3="orange";"stehen";"fahren"))
Thanks to the nesting, not only " red " but also " orange " can now be queried. Expressed in words, the result is as follows: IF the 1st condition "red" is fulfilled; THEN I will "stand"; if this is not the case, THEN I will, IF the 2nd condition "Orange" is fulfilled; "stand"; ELSE I will "drive".
Note: Pay attention to the correct number of brackets when functions are nested in one another to avoid error messages.