Formulas and Functions

Chapter 13 Additional Examples and Topics 359
Expression 1
=AND(B2>60, B2<=75) tests for a low score. If the test score is in the range 61 to 75, AND will return
TRUE meaning the student should come in for a special study session. Otherwise it will return FALSE.
Expression 2
=OR(ISBLANK(B2), B2<0, B2>100) tests for invalid data. The rst OR expression “ISBLANK(B2)” will be
TRUE if there is no test score. The second expression will return TRUE if the test score is negative and
the third expression will return TRUE if the test score is over 100. The OR will return TRUE if any of the
conditions is TRUE, meaning the data is invalid in some way. The OR will return FALSE if none of the
conditions are TRUE and therefore the data is valid.
Expression 3
=B2<=60 tests for a failing grade. This expression will return TRUE if the test score is 60 or below, a
failing grade. Otherwise it returns FALSE.
Putting it together in an IF function
=IF(AND(B2>60, B2<=75), “Needs study session”, IF(OR(ISBLANK(B2), B2<0, B2>100), “Invalid data”,
IF(B2<=60, “Exam failed”, “”)))
If the test expression (same as Expression 1 above) in the rst IF evaluates to TRUE, the function will
return “Needs study session”; otherwise it will continue to the FALSE argument, the second IF.
If the test expression (same as Expression 2 above) of the second IF evaluates to TRUE, the function
will return “Invalid data”; otherwise it will continue to the FALSE argument, the third IF.
If the test expression (same as Expression 3 above) of the third IF evaluates to TRUE, the function will
return “Exam failed”; otherwise the expression will return an empty expression (“”).
The result might look like the following table.