Microsoft Office Tutorials and References
In Depth Information
IF returns a value if one condition is TRUE , and returns another value if the condition is
The IF function is one of the most commonly used logical functions in Excel. This function
can be used in conjuction with other formulas, embedded within itself to perform up to seven
logical tests per cell, or used with cell referencing to apply text results. We’ve already seen
the IF function in action in the AND function example. Notice the example in Figure 8.3. There
are three different logics used in conjuction with IF . The first example applies the logic of
whether a student has passed or failed. The result is applied to cell E4. The formula in E4—
=IF(D4=”PASS”,”GRADE 7”,”GRADE 6”) —states, if the text result is in cell D4 = “PASS” then
apply the result GRADE 7 else GRADE 6. The first value applied is the true value, and the
second value applied is the false value based on whether the logical test is met.
This is a value that can be evaluated in a True or False condition.
This is the True result if the condition is met. If omitted, assumes
True. The value if True can also be another formula.
VALUE IF TRUE
This is the False result if the condition is not met. If omitted,
assumes False. The value if False can also be another formula.
VALUE IF FALSE
Applied text result
Applied formula result
The IF formula
performs the logical
test and applies the
true result value first
after the logical test is
met and the false value
following the true value.
Embedded IF statement with text result
Notice the second example in Figure 8.3. The formula in cell F16 asks the logical question—
if cell E16 equals zero, then apply zero, else apply the formula E16 + F16 . The last example in
Figure 8.3 shows an automated grading formula with an embedded IF statement. The formula
applies the text results of the grades A–F based on the whether the logical test is met. The
formula is structured in a way that there is only one false value and that value is F and only
occurs if all other true values are not met. The formula— =IF(D25>89,”A”,IF(D25>80,”B”,
IF(D25>70,”C”,IF(D25>60,”D”,”F”)))) —states the following conditions: