Multiple IF functions can be nested to allow for multiple criteria. With Excel’s IF function, you can perform a logical comparison between a value and the value you expect, testing for a condition and returning a result if “True” or “False”.
You are watching : what is an if else
The IF function allows a logical comparison between a current value and an expected value. It does this by checking for a condition and returning a result if the value is true or false.
-
IF(a value is true, do this, else do something else)
Therefore, an IF statement can have two results. The first result is returned if the comparison is true, the second if the comparison is false.
IF statements are incredibly robust and form the basis of many spreadsheet models, but they are also at the root of many spreadsheet problems. Ideally, an IF statement should apply to minimal conditions (e.g. Male/Female, Yes/No/Maybe to name a few), but sometimes more complex scenarios need to be evaluated involving the nesting* of more than 3 IF -Functions require.
* “Nesting” is the merging of multiple functions into one formula.
Use the IF function, one of the logical functions, to return one value when a condition is met and a different value when the condition is not met.
Syntax
IF(check;then_value;[else_value])
Example:
-
=IF(A2>B2;”Over budget”;”OK”)
-
=IF(A2=B2;B4-A4;””)
Argument Name
Description
Check
(required)
The condition to check.
then_value
(required)
The value to return if the result of the is TRUE.
else_value
(optional)
The value to return if the result of the is FALSE.
Notes
Although Excel allows nesting of up to 64 different IF functions, this is not advisable. Why?
-
Multiple IF statements require a great deal of concentration to be constructed correctly and to ensure that their logic is properly calculated through each condition to the end. If the formula is not nested 100% correctly, it may only work 75% of the time and return unexpected results 25% of the time. Unfortunately, catching that problematic 25% is almost impossible.
-
Multiple IF statements can be incredibly difficult to manage, especially if you come back to them later and try to figure out what you, or worse, someone else was trying to accomplish.
If you have an IF statement that just seems to keep growing with no end in sight, put your mouse down and reconsider your strategy.
Let’s see how to properly construct a complex nested IF statement with multiple IF functions, and when to use another tool from Excel’s arsenal.
Examples
The following is an example of a fairly standard nested IF statement that converts student test results into the lettered ratings.
