Discover how to use IF function in a formula.
When to use
IF function is one of the most important functions in Excel. The syntax for IF demonstrates just how useful this function is for financial modeling.
- Returns one value if a condition you specify evaluates to TRUE and another value if it evaluates to FALSE
- Syntax: IF(logical_test,value_if_true,value_if_false)
- logical _test is any value or expression that can be evaluated to TRUE or FALSE
- value _if_true is the value that is returned if logical_test is TRUE
- value _if_false is the value that is returned if logical_test is FALSE
- In this example, the intention is to evaluate the quotient Numerator / Denominator. However, if the Denominator is either blank or zero, this will result in an #DIV/0! error. Excel has several errors that it cannot evaluate, e.g. #REF!, #NULL, #N/A.
- This is where IF comes in. In the example above, =IF(Denominator=0,,Numerator/Denominator) tests whether the Denominator is zero, If so, the value is unspecified (blank) and will consequently return a value of zero in Excel. Otherwise, the quotient is calculated as intended.
- This is known as creating an error trap. Errors are “trapped” and the ‘harmless’ value of zero is returned instead. You could put “n.a” or “This is an error” as the Value_if_TRUE.
- Flags frequently make models more transparent. It is usually better to step out a calculation than to condense a model into fewer cells. If it can be followed on a piece of paper (without access to the formula bar), then it will be easier to follow. If more can follow the model logic, errors will be more easily spotted. When this occurs, a model becomes trusted and therefore is of more value in decision-making.
- Sometimes you just can’t use flags. Here, the flag does not trap the division by zero error. This is because this formula evaluates to =#DIV/0! x 0, which equals #DIV/0! If you need to trap an error, you must use an IF function.
Lesson notes are only available for subscribers.