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.
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
- Formula: a mathematical operation you define, using constants or references separated by operators
- Reference: a pointer to another location (cell or range). References can be names or addresses.
=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.
- Operator: a symbol representing a mathematical manipulation. Common operators are + (addition), - (subtraction), * (multiplication), / (division)
- Parentheses () are used to group sections of a formula or a function
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
- Kludgy to create if there are more than just a few elements in the formula, vs. a function that can take a multi-cell range as a reference.
- Not flexible over time--do not adjust to accept new columns or rows that are added to the worksheet.
A function is a pre-defined mathematical operation which has a name and performs its magic on the data or references it is fed.
- Function: a predefined mathematical operation built-in to Excel. Functions have names and parentheses, which contain the constants or references required by the function.
- Arguments: the function does its work on what it is given in its parentheses. One or more arguments are provided to the function to work on. An argument can be a reference, a number, a name, text, or even another function or formula. When there are more than one argument, they are separated by commas.
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%)