To apply a function, you create a formula that uses that function. If you know the formula you want to use, you can type it directly into a worksheet cell, or into the Formula Bar (which displays the cell content in a larger space). If you’re uncertain about the specific function that will perform the calculation you want, or unsure about how to put together a formula using that function, you can call on the Formula Builder to help you put one together.
You can create a formula in any of these three ways:
Type the formula directly into the worksheet cell in which you want the results to appear.- Display the Formula Bar, and type the formula into the box.
As you enter the formula, prompts appear, to guide you through the formula-creation process.
- Display the Formula Builder in the Toolbox, and select the function you want to use.
After you select the function, the correct syntax appears in the Description box.
Double-clicking a function displays boxes at the bottom of the Formula Builder into which you can enter the required information. As you enter it, the Formula Builder builds the formula in the cell and (if it’s displayed) in the Formula Bar.
Formulas in an Excel worksheet most often involve functions performed on the values contained in one or more other cells on the worksheet (or on another worksheet). In this chapter, when we refer to a cell in a formula, we’ll call that cell the reference cell.
The basis of every formula is an equal sign (=). This symbol at the beginning of the cell content notifies Excel that the text following the equal sign is not what you want to display in the cell. The simplest formula takes the form of an equal sign followed by a cell reference. For example, this formula displays the contents of the reference cell A5:
=A5
Tip
You don’t have to use a reference cell in a formula—you can perform functions on actual alphanumeric data. The beauty of performing functions on reference cells is that you can change the results of the function by changing the content of the reference cell, without having to edit the formula.
The next-simplest formula is one that performs a mathematical calculation based on the contents of one reference cell, but without using a function. For example:
=A5+1
returns a number, which could also be a date or time, that is one larger than the number in cell A5.
Formulas that use functions are built in this standard format:
=FUNCTION(argument1,argument2)
In this case, argument represents a reference cell, number, or other value that is required by the function. For example:
=MAX(A5,B5)
returns the value of cell A5 or of cell B5, whichever is larger.
Notice that the two arguments in the preceding example are separated by a comma. Excel evaluates whatever information is between the commas as one argument, so you can perform multiple calculations within a formula. For example:
=NETWORKDAYS(C2,TODAY())
returns the number of workdays (not counting weekends and holidays) between the date in cell C2 and today’s date.
Tip
The TODAY function returns today’s date. The function requires a set of parentheses following the name.
You can reference a range of cells in a formula by referencing the first and last cells in the range, divided by a colon. For example:
=AVERAGE(A1:B5)
returns the average of the values of the 10 cells included in the specified range (A1 through A5 and B1 through B5).
When you reference a cell or range of cells in a formula, you can enter the cell reference by typing it or—with the insertion point active in the formula where you want to insert the reference—by clicking a cell or dragging to select a range of cells.