Excel lets you use a variety of operators in your formulas. Operators are symbols that indicate the type of mathematical operation you want the formula to perform. Table 11.1 lists the operators that Excel recognizes. In addition to these, Excel has many built-in functions that enable you to perform additional calculations.

You can, of course, use as many operators as you need to perform the desired calculation.

Following are some examples of formulas that use various operators.

When Excel calculates the value of a formula, it uses certain rules to determine the order in which the various parts of the formula are calculated. You need to understand these rules if you want your formulas to produce the desired results.

Table 11.2 lists the Excel operator precedence. This table shows that exponentiation has the highest precedence (it’s performed first) and logical comparisons have the lowest precedence (they’re performed last).

You can use parentheses to override the Excel’s built-in order of precedence. Expressions within parentheses are always evaluated first.

The following formula uses parentheses to control the order in which the calculations occur. In this case, cell B3 is subtracted from cell B2 and the result is multiplied by cell B4:

=(B2-B3)*B4

If you enter the formula without the parentheses, Excel computes a different answer. Because multiplication has a higher precedence, cell B3 is multiplied by cell B4. Then this result is subtracted from cell B2, which isn’t what was intended.

The formula without parentheses looks like this:

=B2-B3*B4

It’s a good idea to use parentheses even when they aren’t strictly necessary. Doing so helps to clarify what the formula is intended to do. For example, the following formula makes it perfectly clear that B3 should be multiplied by B4, and the result subtracted from cell B2. Without the parentheses, you would need to remember Excel’s order of precedence.

=B2-(B3*B4)

You can also nest parentheses within formulas—that is, put them inside other parentheses. If you do so, Excel evaluates the most deeply nested expressions first—and then works its way out. Here’s an example of a formula that uses nested parentheses:

=((B2*C2)+(B3*C3)+(B4*C4))*B6

This formula has four sets of parentheses—three sets are nested inside the fourth set. Excel evaluates each nested set of parentheses and then sums the three results. This result is then multiplied by the value in B6.

Although the preceding formula uses four sets of parentheses, only the outer set is really necessary. If you understand operator precedence, it should be clear that you can rewrite this formula as:

=(B2*C2+B3*C3+B4*C4)*B6

Again, using the extra parentheses makes the calculation much clearer.

Every left parenthesis, of course, must have a matching right parenthesis. If you have many levels of nested parentheses, keeping them straight can sometimes be difficult. If the parentheses don’t match, Excel displays a message explaining the problem—and won’t let you enter the formula.

In some cases, if your formula contains mismatched parentheses, Excel may propose a correction to your formula. Figure 11.1 shows an example of the Formula AutoCorrect feature. You may be tempted simply to accept the proposed correction, but be careful—in many cases, the proposed formula, although syntactically correct, isn’t the formula you intended, and it will produce an incorrect result.

Figure 11.1. Excel’s Formula AutoCorrect feature often suggests a correction to an erroneous formula.

Most formulas you create use worksheet functions. These functions enable you to greatly enhance the power of your formulas and perform calculations that are difficult (or even impossible) if you use only the operators discussed previously. For example, you can use the TAN function to calculate the tangent of an angle. You can’t do this calculation by using only the mathematical operators.

=(A1+A2+A3+A4+A5+A6+A7+A8+A9+A10)/10

=AVERAGE(A1:A10)

=MAX(A1:D100)

=PROPER(A1)

=IF(A1<100000,A1*5%,A1*7.5%)

=NOW()

Entering a formula manually involves, well, entering a formula manually. In a selected cell, you simply type an equal sign (=) followed by the formula. As you type, the characters appear in the cell and in the Formula bar. You can, of course, use all the normal editing keys when entering a formula.

Even though you can enter formulas by typing in the entire formula, Excel provides another method of entering formulas that is generally easier, faster, and less error-prone. This method still involves some manual typing, but you can simply point to the cell references instead of typing their values manually. For example, to enter the formula =A1+A2 into cell A3, follow these steps:

If your formula uses named cells or ranges, you can either type the name in place of the address or choose the name from a list and have Excel insert the name for you automatically. Two ways to insert a name into a formula are available:

Figure 11.3 shows an example. The worksheet contains two defined names: Expenses and Sales. The Paste Name dialog box is being used to insert a name (Sales) into the formula being entered in cell B11.

Figure 11.3. You can use the Paste Name dialog box to quickly enter a defined name into a formula.

The easiest way to enter a function into a formula is to use the drop-down list that Excel displays while you type a formula. In order to use this method, however, you must know at least the first character of the function’s name.

Another way to insert a function is to use the Function Library group on the Formulas tab (see Figure 11.4). This is especially useful if you can’t remember which function you need. Click the function category (Financial, Logical, Text, etc.) and you’ll get a list of the function in that category. Click the function you want, and Excel displays its Function Arguments dialog box. This is where you enter the function’s arguments. In addition, you can click the Help On This Function link to learn more about the selected function.

Figure 11.4. You can insert a function by selecting it from one of the function categories.

Yet another way to insert a function into a formula is to use Excel’s Insert Function dialog box (see Figure 11.5). You can access this dialog box in several ways:

Figure 11.5. The Insert Function dialog box.

The Insert Function dialog box shows a drop-down list of function categories. Select a category, and the functions in that category are displayed in the list box. To access a function that you’ve used recently, select Most Recently Used from the drop-down list.

If you’re not sure which function you need, you can search for the appropriate function by using the Search For A Function box at the top of the dialog box. Enter your search terms, click Go, and you’ll get a list of relevant functions. When you select a function in the Select A Function list box, Excel displays the function (and its argument names) in the dialog box along with a brief description of what the function does.

When you locate the function you want to use, highlight it and click OK. Excel then displays its Function Arguments dialog box, as shown in Figure 11.6. Use this dialog box to specify the arguments for the function. The dialog box will vary, depending on the function you’re inserting, and it will show one text box for each of the function’s arguments. To use a cell or range reference as an argument, you can enter the address manually or click inside the argument box and then select (that is, point to) the cell or range in the sheet). After you’ve specified all the function arguments, click OK.

Figure 11.6. The Function Arguments dialog box.

Following are some additional tips to keep in mind when you use the Insert Function dialog box to enter functions:

When you use a cell (or range) reference in a formula, you can use three types of references:

An absolute reference uses two dollar signs in its address: one for the column letter and one for the row number (for example, $A$5). Excel also allows mixed references in which only one of the address parts is absolute (for example, $A4 or A$4).

By default, Excel creates relative cell references in formulas. The distinction becomes apparent when you copy a formula to another cell.

Figure 11.7 shows a simple worksheet. The formula in cell D2, which multiplies the quantity by the price, is

=B2*C2

Figure 11.7. Copying a formula that contains relative references.

This formula uses relative cell references. Therefore, when the formula is copied to the cells below it, the references adjust in a relative manner. For example, the formula in cell D3 is

=B3*C3

But what if the cell references in D2 contained absolute references, like this?

=$B$2*$C$2

In this case, copying the formula to the cells below would produce incorrect results. The formula in cell D3 would be exactly the same as the formula in cell D2.

Now I’ll extend the example to calculate sales tax, which is stored in cell B7 (see Figure 11.8). In this situation, the formula in cell D2 is

=B2*C2*$B$7

Figure 11.8. Formula references to the sales tax cell should be absolute.

The quantity is multiplied by the price, and the result is multiplied by the sales-tax rate stored in cell B7. Notice that the reference to B7 is an absolute reference. When the formula in D2 is copied to the cells below it, cell D3 will contain this formula:

=B3*C3*$B$7

Here, the references to cells B2 and C2 were adjusted, but the reference to cell B7 was not—which is exactly what I want.

Figure 11.9 demonstrates the use of mixed references. The formulas in the C3:F7 range calculate the area for various lengths and widths. The formula in cell C3 is

=$B3*C$2

Figure 11.9. Using mixed cell references.

Notice that both cell references are mixed. The reference to cell B3 uses an absolute reference for the column ($B), and the reference to cell C2 uses an absolute reference for the row ($2). As a result, this formula can be copied down and across, and the calculations will be correct. For example, the formula in cell F7 is

=$B7*F$2

If C3 used either absolute or relative references, copying the formula would produce incorrect results.

You can enter nonrelative references (that is, absolute or mixed) manually by inserting dollar signs in the appropriate positions of the cell address. Or you can use a handy shortcut: the F4 key. When you’ve entered a cell reference (by typing it or by pointing), you can press F4 repeatedly to have Excel cycle through all four reference types.

For example, if you enter =A1 to start a formula, pressing F4 converts the cell reference to =$A$1. Pressing F4 again converts it to =A$1. Pressing it again displays =$A1. Pressing it one more time returns to the original =A1. Keep pressing F4 until Excel displays the type of reference that you want.

Formulas can also refer to cells in other worksheets—and the worksheets don’t even have to be in the same workbook. Excel uses a special type of notation to handle these types of references.

SheetName!CellAddress

=A1*Sheet2!A1

=A1*'All Depts'! A1

=[WorkbookName]SheetName!CellAddress

=[Budget.xlsx]Sheet1!A1

=A1*'[Budget For 2008.xlsx]Sheet1'!A1

=A1*'C:My Documents[Budget For 2008.xlsx]Sheet1'!A1

='\DataServerfiles[budget.xlsx]Sheet1'!$D$7

Figure 11.10 shows a simple table with three columns. I entered the data, and then converted the range to a table by choosing Insert Tables Table. Note that I didn’t define any names, but the table is named Table1 by default.

Figure 11.10. A simple table with three columns.

If you’d like to calculate the total projected and total actual sales, you don’t even need to write a formula. Simply click a button to add a row of summary formulas to the table:

For the SUBTOTAL function, 109 is an enumerated argument that represents SUM. The second argument for the SUBTOTAL function is the column name, in square brackets. Using the column name within brackets is a new way to create “structured” references within a table. (I discuss this further in an upcoming section, “Referencing data in a table.”)

In many cases, you’ll want to use formulas within a table. For example, in the table shown in Figure 11.11, you may want a column that shows the difference between the Actual and Projected amounts. As you’ll see, Excel 2007 makes this very easy.

Figure 11.12 shows the table with the new column.

Figure 11.12. The Difference column contains a formula.

If you examine the table, you’ll find this formula for all cells in the Difference column:

=[Actual]-[Projected]

Although the formula was entered into the first row of the table, that’s not necessary. Any time a formula is entered into an empty table column, it will automatically fill all the cells in that column. And if you need to edit the formula, Excel will automatically copy the edited formula to the other cells in the column.

The steps listed above used the pointing technique to create the formula. Alternatively, you could have entered it manually using standard cell references. For example, you could have entered the following formula in cell E3:

=D3-C3

If you type the cell references, Excel will still copy the formula to the other cells automatically.

One thing should be clear, however, about formulas that use the column headers: They are much easier to understand.

Excel 2007 adds some news ways to refer to data that’s contained in a table by using the table name and column headers. There is no need to create names for these items. The table itself has a name (for example, Table1), and you can refer to data within the table by using column headers.

You can, of course, use standard cell references to refer to data in a table, but the new method has a distinct advantage: The names adjust automatically if the table size changes by adding or deleting rows.

Refer to the table shown in Figure 11.11. This table was given the name Table1 when it was created. To calculate the sum of all the data in the table, use this formula:

=SUM(Table1)

This formula will always return the sum of all the data, even if rows or columns are added or deleted. And if you change the name of Table1, Excel will adjust formulas that refer to that table automatically. For example, if you renamed Table1 to be AnnualData (by using the Name Manager), the preceding formula would be changed to:

=SUM(AnnualData)

Most of the time, you’ll want to refer to a specific column in the table. The following formula returns the sum of the data in the Actual column:

=SUM(Table1[Actual])

Notice that the column name is enclosed in square brackets. Again, the formula adjusts automatically if you change the text in the column heading.

Even better, Excel provides some helpful assistance when you create a formula that refers to data within a table. Figure 11.13 shows the formula autocomplete helping to create a formula by showing a list of the elements in the table.

Figure 11.13. The formula autocomplete feature is useful when creating a formula that refers to data in a table.

When you’re entering formulas, you may occasionally see a Circular Reference Warning message, shown in Figure 11.14, indicating that the formula you just entered will result in a circular reference. A circular reference occurs when a formula refers to its own value—either directly or indirectly. For example, you create a circular reference if you enter =A1+A2+A3 into cell A3 because the formula in cell A3 refers to cell A3. Every time the formula in A3 is calculated, it must be calculated again because A3 has changed. The calculation could go on forever.

Figure 11.14. If you see this warning, you know that the formula you entered will result in a circular reference.

When you get the circular reference message after entering a formula, Excel gives you two options:

Regardless of which option you choose, Excel displays a message in the left side of the status bar to remind you that a circular reference exists.

Usually, a circular reference is quite obvious—easy to identify and correct. But when a circular reference is indirect—as when a formula refers to another formula that refers to yet another formula that refers back to the original formula—it may require a bit of detective work to get to the problem.

Intentional Circular References

You can sometimes use a circular reference to your advantage. For example, suppose a company has a policy of contributing 5 percent of its net profit to charity. The contribution itself, however, is considered an expense—and is therefore subtracted from the net profit figure. This produces a circular reference (see the accompanying figure).

The Contributions cell contains the following formula:

=5%*Net_Profit

The Net Profit cell contains the following formula:

=Gross_Income-Expenses-Contributions

These formulas produce a resolvable circular reference. If the Enable Iterative Calculation setting is on, Excel keeps calculating until the Contributions value is, indeed, 5 percent of Net Profit. In other words, the result becomes increasingly accurate until it converges on the final solution.

You’ve probably noticed that Excel calculates the formulas in your worksheet immediately. If you change any cells that the formula uses, Excel displays the formula’s new result with no effort on your part. All this happens when Excel’s Calculation mode is set to Automatic. In Automatic Calculation mode (which is the default mode), Excel follows these rules when it calculates your worksheet:

Sometimes, however, you may want to control when Excel calculates formulas. For example, if you create a worksheet with thousands of complex formulas, you’ll find that processing can slow to a snail’s pace while Excel does its thing. In such a case, set Excel’s calculation mode to Manual—which you can do by choosing Formulas Calculation Calculation Options Manual (see Figure 11.15)

Figure 11.15. You can control when Excel calculates formulas.

When you’re working in Manual Calculation mode, Excel displays Calculate in the status bar when you have any uncalculated formulas. You can use the following shortcut keys to recalculate the formulas:

Many Excel users don’t realize that you can give a name to an item that doesn’t appear in a cell. For example, if formulas in your worksheet use a sales-tax rate, you would probably insert the tax-rate value into a cell and use this cell reference in your formulas. To make things easier, you would probably also name this cell something similar to SalesTax.

Here’s how to provide a name for a value that doesn’t appear in a cell:

You just created a name that refers to a constant rather than a cell or range. Now if you type =SalesTax into a cell that’s within the scope of the name, this simple formula returns 0.075—the constant that you defined. You also can use this constant in a formula, such as =A1*SalesTax.

Just as you can create a named constant, you can also create named formulas. As with named constants, named formulas don’t appear in the worksheet.

You create named formulas the same way you create named constants—by using the New Name dialog box. For example, you might create a named formula that calculates the monthly interest rate from an annual rate; Figure 11.16 shows an example. In this case, the name MonthlyRate refers to the following formula:

=Sheet1!$B$1/12

Figure 11.16. Excel lets you give a name to a formula that doesn’t exist in a worksheet cell.

When you use the name MonthlyRate in a formula, it uses the value in B1 divided by 12. Notice that the cell reference is an absolute reference.

Naming formulas gets more interesting when you use relative references rather than absolute references. When you use the pointing technique to create a formula in the Refers To box of the New Name dialog box, Excel always uses absolute cell references—which is unlike its behavior when you create a formula in a cell.

For example, activate cell B1 on Sheet1 and create the name Cubed for the following formula:

=Sheet1!A1^3

In this example, the relative reference points to the cell to the left of the cell in which the name is used. Therefore, make certain that cell B1 is the active cell before you open the New Name dialog box; this is very important. The formula contains a relative reference; when you use this named formula in a worksheet, the cell reference is always relative to the cell that contains the formula. For example, if you enter =Cubed into cell D12, then cell D12 displays the contents of cell C12 raised to the third power (C12 is the cell directly to the left of D12).

This section describes a concept known as range intersections—individual cells that two ranges have in common. Excel uses an intersection operator—a space character—to determine the overlapping references in two ranges. Figure 11.17 shows a simple example.

Figure 11.17. You can use a range-intersection formula to determine values.

The formula in cell B9 is

=B1:B6 A3:D3

This formula returns 10, the value in cell B3—that is, the value at the intersection of the two ranges.

The intersection operator is one of three reference operators used with ranges. Table 11.4 lists these operators.

The real value of knowing about range intersections is apparent when you use names. Examine Figure 11.18, which shows a table of values. I selected the entire table and then used Formulas Defined Names Create From Selection to create names automatically by using the top row and left column.

Figure 11.18. When you use names, using a range-intersection formula to determine values is even more useful.

Excel created the following names:

With these names defined, you can create formulas that are easy to read and use. For example, to calculate the total for Quarter 4, just use this formula:

=SUM(Qtr4)

To refer to a single cell, use the intersection operator. Move to any blank cell and enter the following formula:

=Qtr1 West

This formula returns the value for the first quarter for the West region. In other words, it returns the value that exists where the Qtr1 range intersects with the West range. Naming ranges in this manner can help you create very readable formulas.

When you create a name for a cell or a range, Excel doesn’t automatically use the name in place of existing references in your formulas. For example, suppose you have the following formula in cell F10:

=A1-A2

If you define a name Income for A1 and Expenses for A2, Excel won’t automatically change your formula to =Income-Expenses. Replacing cell or range references with their corresponding names is fairly easy, however.

To apply names to cell references in formulas after the fact, start by selecting the range that you want to modify. Then choose Formulas Defined Names Define Name Apply Names. Excel displays the Apply Names dialog box, as shown in Figure 11.19. Select the names that you want to apply by clicking them and then click OK. Excel replaces the range references with the names in the selected cells.

Figure 11.19. Use the Apply Names dialog box to replace cell or range references with defined names.

When you create a formula, think twice before you use any specific value in the formula. For example, if your formula calculates sales tax (which is 6.5 percent), you may be tempted to enter a formula, such as the following:

+A1*.065

A better approach is to insert the sales tax rate in a cell—and use the cell reference. Or you can define the tax rate as a named constant, using the technique presented earlier in this chapter. Doing so makes modifying and maintaining your worksheet easier. For example, if the sales tax rate changed to 6.75 percent, you would have to modify every formula that used the old value. If you store the tax rate in a cell, however, you simply change that one cell—and Excel updates all the formulas.

If you simply need to perform a calculation, you can use the Formula bar as a calculator. For example, enter the following formula—but don’t press Enter:

=(145*1.05)/12

If you press Enter, Excel enters the formula into the cell. But because this formula always returns the same result, you may prefer to store the formula’s result rather than the formula itself. To do so, press F9—and watch the result appear in the Formula bar. Press Enter to store the result in the active cell. (This technique also works if the formula uses cell references or worksheet functions.)

When you copy a formula, Excel adjusts its cell references when you paste the formula to a different location. Sometimes, you may want to make an exact copy of the formula. One way to do this is to convert the cell references to absolute values, but this isn’t always desirable. A better approach is to select the formula in Edit mode and then copy it to the Clipboard as text. You can do this in several ways. Here’s a step-by-step example of how to make an exact copy of the formula in A1—and copy it to A2:

You also can use this technique to copy just part of a formula, if you want to use that part in another formula. Just select the part of the formula that you want to copy by dragging the mouse, and then use any of the available techniques to copy the selection to the Clipboard. You can then paste the text to another cell.

Formulas (or parts of formulas) copied in this manner won’t have their cell references adjusted when they are pasted to a new cell. That’s because the formulas are being copied as text, not as actual formulas.

If you have a range of formulas that will always produce the same result (that is, dead formulas), you may want to convert them to values. If, say, range A1:A20 contains formulas that have calculated results that will never change—or that you don’t want to change. For example, if you use the RANDBETWEEN function to create a set of random numbers and you don’t want Excel to recalculate those random numbers each time you press Enter, you can convert the formulas to values. Just follow these steps: