The IF Function in Excel
2010-11-16
The IF Function is used to evaluate a logical test, then if TRUE do something or if FALSE do something else. A familiar construct. The IF Function has three arguments:
IF(logical_test, [value_if_true], [value_if_false])
Argument 1
The first argument, logical_test, is required. It's any value or expression that can be evaluated to TRUE or FALSE. This argument can use any comparison calculation operator. There are four types:
- Arithmetic operators ( + - * / % ^ )
- Comparison operators ( = > < >= <= >< )
- Text concatenation operator ( & )
- Reference operators (colon, comma, space )
Argument 2
The second argument, value_if_true, is optional. This value is returned if the logical_test argument evaluates to TRUE.
I didn't know this second argument was OPTIONAL, but nevertheless you can omit this argument by placing a comma after the first argument, followed by nothing. An example would be =IF(A1= 5,). But here's the weird part, if the second argument is omitted and TRUE, the function returns a zero (0), and if the second argument is omitted and FALSE the function returns FALSE.
When TRUE = 1 and FALSE = 0 then everything is right with the Excel world. When TRUE = 0 then things are messed up. I think they messed up here. Although how many people omit the second argument in an IF statement? But I digress.
Argument 3
The third argument, value_if_false, is optional. This value is returned if the logical_test argument evaluates to FALSE.
Two Common Uses for the IF Function
Division by zero is undefined mathematically so Excel provides a glaring #DIV/0! when that happens. And sometimes we don't want to show the result of a formula unless there's some actual data to calculate.
Avoid Division by Zero
The intent for column D is to divide Num1 by Num 2, but avoid division by zero. The IF function is looking to see if Num 2 is zero (0) and if so, then zero(0) is returned, otherwise the calculation is done (Num 1 / Num 2). The formula in cell D2 is =IF(C2=0,0,B2/C2).
Knowing what I do now about omitting the second argument, this formula could be re-written as =IF(C2=0,,B2/C2) with the same result. However, not a very good practice.
More commonly this formula is written differently as =IF(C2<>0,B2/C2,0) with the same result; doing the calculation if Num 2 is not zero (0) and returning a zero (0) when it is.
Calculate or Leave Blank
Many times formulas extend beyond currently entered data. This is where the IF function can be useful. The formula in cell D2 is =IF(C2<>"",B2+C2,""), which adds Num 1 and Num 2 if Num2 is not blank, and returns a blank ("") otherwise.
This formula could also be written =IF(C2="","",B2+C2) which reads like this: if C2 is blank, then return a blank, otherwise add B2 and C2. Both of these formulas give the same result.