Microsoft Office Tutorials and References

In Depth Information

**Using Functions in Formulas**

✓
Trying to reference a nonexistent cell, range, worksheet, or workbook

✓
Entering the wrong type of information into an argument function

This is by no means an exhaustive list of possible error conditions, but you

get the idea. So what does Excel do about it? There are a handful of errors

that Excel places into the cell with the problem formula.

Error Type

When It Happens

When you’re trying to divide by 0

#DIV/0!

#N/A!

When a formula or a function inside a formula cannot find the

referenced data

When text in a formula is not recognized

#NAME?

When a space was used instead of a comma in formulas that

reference multiple ranges; a comma is necessary to separate

range references

#NULL!

When a formula has numeric data that is invalid for the

operation type

#NUM!

#REF!

When a reference is invalid

#VALUE!

When the wrong type of operand or function argument 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: