Microsoft Office Tutorials and References

In Depth Information

**Functions**

Functions

that the
source cells
(that is, the cells being referred
to) use, and applies that to the

cell with the formula. If you add two numbers and you’ve formatted both with the

Currency number format, then your result also has the Currency number format.

Of course, you’re always free to change the formatting of the cell after you’ve entered

the formula.

Usually, Excel’s automatic formatting is quite handy. Like all automatic features,

however, it’s a little annoying if you don’t understand how it works when it springs

into action. Here are a few points to consider:

• Excel copies only the number format to the formula cell. It ignores other details,

like fonts, fill colors, alignment, and so on.

• If your formula uses more than one cell reference, and the different cells use

different number formats, Excel uses its own rules of precedence to decide which

number format to use. For example, if you add a cell that uses the Currency

number format with one that uses the Scientific number format, then the

destination cell has the Scientific number format. Sadly, these rules aren’t spelled out

anywhere, so if you don’t see the result you want, it’s best to just set your own

formatting.

• If you change the formatting of the source cells
after
you enter the formula, it

won’t have any effect on the formula cell.

• Excel copies source cell formatting only if the cell that contains the formula

uses the General number format (which is the format that all cells begin with).

If you apply another number format to the cell
before
you enter the formula,

then Excel doesn’t copy any formatting from the source cells. Similarly, if you

change a formula to refer to new source cells, then Excel doesn’t copy the format

information from the new source cells.

Functions

A good deal of Excel’s popularity is due to the collection of
functions
it provides.

Functions are built-in, specialized algorithms that you can incorporate into your

own formulas to perform powerful calculations. Functions work like miniature

computer programs—you supply the data, and the function performs a calculation

and gives you the result.

In some cases, functions just simplify calculations that you could probably perform

on your own. For example, most people know how to calculate the average of several

values, but when you’re feeling a bit lazy, Excel’s built-in AVERAGE() function

automatically gives you the average of any cell range. Even more usefully, Excel functions

perform feats that you probably wouldn’t have a hope of coding on your own,

including complex mathematical and statistical calculations that predict
trends
—hid-

den relationships in your data that you can use to make guesses or predict the future.

Every function provides a slightly different service. For example, one of Excel’s

statistical functions is named COMBIN(). It’s a specialized tool used by probability

mathematicians to calculate the number of ways a set of items can be combined.