Basics of formulas and functions

Formulas and functions both perform mathematical operations, and provide the result as a value. They just do it slightly differently: formulas are very specific and are useful for quick-and-dirty, simple calculations; functions work with broader strokes and tend to be useful in larger or more complex scenarios.

Formulas ("Build it yourself")

A formula is simply a mathematical operation, where every element is "spelled out." For example, the following entry in an Excel cell is a formula that would produce the result 1600:

=100+300+500+700

=Principal*Interest

If the numbers in the formula above were stored in cells B4, B5, B6, and B7, we could rewrite the formula with references:

=B4+B5+B6+B7

References add flexibility to formulas. "Hard-wiring" numbers into formulas is not a best practice.

If you aren't familiar with operators, or the concept of precedence (that multiplication and division are performed before addition and subtraction, for example), read about operator precedence in your books or on-line help manuals.

Formulas have the advantage of being straightforward and simple, but they become inefficient and inflexible as they get more complex. For example, the following formula calculates the average of four cells

=(B4+B5+B6+B7)/4

You can imagine it is kludgy to create complex formulas, and that's where functions come in.

Disadvantages of formulas

Functions ("Feed the function")

A function is a pre-defined mathematical operation which has a name and performs its magic on the data or references it is fed.

For example, the SUM function performs addition. We could rewrite our first formula as a function and provide the four numbers as arguments.

=SUM(100,300,500,700)

The function above is identical to our first formula. And it looks just as kludgy, if not worse. Once references are used, though, functions begin to shine, because you can give the function a range to work on.

=SUM(B4:B7)

Other examples of common functions show the use of named references:

=SUM(Sales)

=AVERAGE(B4:B7)

=AVERAGE(Sales)

=IF(Sales>=100,Sales*10%,Sales*5%)