Apple iWork '09 - Manual (Page 360)

Trapping Division by Zero
Sometimes it is not possible to construct a table in a manner that can avoid division
by zero. However, if division by zero occurs, the result is an error value in the cell, which
is usually not the desired result. This example shows three methods of preventing this
error.
Examples
Assume that cell D2 and E2 each contain a number. It is possible that E2 contains 0. You wish to
divide D2 by E2, but avoid a division by zero error. Each of the following three methods will return 0 if
cell E2 is equal to zero; otherwise each returns the result of D2/E2.
=IF(E2=0,0,D2/E2) operates by directly testing cell E2 to see if it is 0.
=IFERROR(D2/E2,0) operates by returning 0 if an error occurs. Division by zero is an error.
=IF(ISERROR(D2/E2),0,D2/E2) operates by doing a logical test to see if D2/E2 is TRUE.
Specifying Conditions and Using Wildcards
Some functions, such as SUM, operate on entire ranges. Other functions, such as SUMIF,
only operate on the cells in the range that meet a condition. For example you might
want to add up all the numbers in column B that are less than 5. To do this, you could
use =SUMIF(B, “<5”). The second argument of SUMIF is called a condition because it
causes the function to ignore cells that do not meet the requirements.
There are two types of functions that take conditions. The first type is functions that
have names ending in IF or IFS (except for the function IF, which does not take a
condition; it instead takes an expression that should evaluate to either TRUE or FALSE).
These functions can do numeric comparisons in their conditions, such as “>5”, “<=7”,
or “<>2”. These functions also accept wildcards in specifying conditions. For example,
to count the number of cells in column B that begin with the letter “a”, you could use
=COUNTIF(B, “a*”)
The second group of functions take conditions, such as HLOOKUP, but can’t do
numeric conditions. These functions sometimes permit the use of wildcards.
Function Allows numeric comparisons Accepts wildcards
AVERAGEIF yes yes
AVERAGEIFS yes yes
COUNTIF yes yes
COUNTIFS yes yes
SUMIF yes yes
SUMIFS yes yes
360 Chapter 13 Additional Examples and Topics