Fundamentals of formulas and functions
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
- 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
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
- 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.
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.
- 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%)
Foolproof rules for creating formulas and functions
- Name first: Name cells and ranges before creating formulas & functions that refer to them
- Start with equal (=)
- End with ENTER
- Use your mouse to select references, or use a name
- Whenever possible, use your mouse to select cells. Do not type cell addresses or ranges as doing so is kludgy and potentially inaccurate.
- Click/drag on the worksheet to select a cell or range when the reference should change as you fill the formula (i.e. a relative reference)
- Select a named cell or range if the reference should stay the same as you fill the formula (i.e. an absolute or mixed reference). Or press F3 or choose Insert → Name ? Paste to select a name. It's OK to break the rule and type the name, rather than using your mouse. But only when it's a named cell or range--no typing addresses!!
- AutoFill the resulting formula: If you've entered a formula properly, you can then AutoFill or copy/paste it, and it will be correct.
Using names in formulas and functions
You can use names in formulas and functions by:
- Typing the name.
- Pressing F3 -- or the Insert → Name ? Paste command -- and selecting from the Paste Name dialog box
If a name exists only once in a workbook, the name is specific to that location in the book and is available "book-wide." If a name exists on more than one sheet, the name becomes sheet specific. It ends up working just like you would hope it does!
If you use the F3 -- or the Insert ? Name ? Paste command, you will see a list of names available for the current worksheet. If you paste a name that exists on more than one worksheet, be sure you know which name has been used. Better yet, use your mouse to select the exact reference or type the name using the syntax: 'SheetName'!Name
Printing Formulas and Functions
There are several options for how to view your formulas and functions on an Excel worksheet:
- View (and print) your formulas by choosing Tools → Options and clicking the View tab. Select Formulas.
- For more advanced formula views, turn on Formula Auditing. Choose Tools ? Formula Auditing ? Formula Auditing Mode (shortcut: ctrl+'). Use the Formula Auditing toolbar to Trace Precedents and Trace Dependents to see what "goes into" each formula and where each cell "goes", math-wise.