Datasheet
Error Type When It Happens
#DIV/0! When you’re trying to divide by 0
#N/A! When a formula or a function inside a formula
cannot find the referenced data
#NAME? When text in a formula is not recognized
#NULL! When a space was used instead of a comma in for-
mulas that reference multiple ranges; a comma is
necessary to separate range references
#NUM! When a formula has numeric data that is invalid for
the operation type
#REF! When a reference is invalid
#VALUE! When the wrong type of operand or function argu-
ment is used
Chapter 4 discusses catching and handling formula errors in detail.
Using Functions in Formulas
Functions are like little utility programs that do a single thing. For example,
the SUM function sums up numbers, the COUNT function counts, and the
AVERAGE function calculates an average.
There are functions to handle many different needs: working with numbers,
working with text, working with dates and times, working with finance, and
so on. Functions can be combined and nested (one goes inside another).
Functions return a value, and this value can be combined with the results
of another function or formula. The possibilities are nearly endless.
But functions do not exist on their own. They are always a part of a formula.
Now that can mean that the formula is made up completely of the function or
that the formula combines the function with other functions, data, operators,
or references. But functions must follow the formula golden rule:
Start with
the equal sign
. Look at some examples:
Function/Formula Result
=SUM(A1:A5) Returns the sum of the values in the range
A1:A5. This is an example of a function serv-
ing as the whole formula.
=SUM(A1:A5) /B5 Returns the sum of the values in the range
A1:A5 divided by the value in cell B5. This is
an example of mixing a function’s result with
other data.
28
Part I: Putting the Fun in Functions
05_046555 ch01.qxp 12/26/06 7:54 PM Page 28