Using operators in formulas

Excel formulas support a variety of operators. Operators are symbols that indicate what mathematical (or logical) operation you want the formula to perform. Table 9.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.

Here are some examples of formulas that use various operators:

Understanding operator precedence in formulas

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 so that your formulas produce accurate results.

Table 9.2 lists the Excel operator precedence. This table shows that exponentiation has the highest precedence (performed first) and logical comparisons have the lowest precedence (performed last).

You can use parentheses to override Excel's built-in order of precedence. Expressions within parentheses are always evaluated first. For example, 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. This result is then subtracted from cell B2, which isn't what was intended.

The formula without parentheses looks like this:

=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 cell 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 follows:

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

Most would agree that using the extra parentheses makes the calculation much clearer.

Note that operators at the same level of precedence, such as multiplication and division, are evaluated from left to right (unless parentheses would indicate a different order).

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 it won't let you enter the formula.

Using functions in your formulas

Many formulas that 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 complicated calculation by using the mathematical operators alone.

Entering formulas manually

Entering a formula manually involves, well, entering a formula manually. In a selected cell, you 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 of the normal editing keys when entering a formula.

Entering formulas by pointing

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:

1. Select cell A3.
2. Type an equal sign (=) to begin the formula. Notice that Excel displays Enter in the status bar (lower left of your screen).
3. Press the up arrow twice. As you press this key, Excel displays a dashed border around cell A1, and the cell reference appears in cell A3 and in the Formula bar. In addition, Excel displays Point in the status bar.
4. Type a plus sign (+). A solid color border replaces the dashed border of A1, and Enter reappears in the status bar.
5. Press the up arrow again. The dashed border encompasses cell A2 and adds that cell address to the formula.
6. Press Enter to complete the formula.

Pasting range names into formulas

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. Three ways to insert a name into a formula are available:

• Select the name from the drop-down list. To use this method, you must know at least the first character of the name. When you're entering the formula, type the first character and then select the name from the drop-down list.
• Press F3. The Paste Name dialog box appears. Select the name from the list and then click OK (or just double-click the name). Excel enters the name into your formula. If no names are defined, pressing F3 has no effect.
• Click the Use in Formula drop-down on the Formulas tab (Defined Names group). This command is available while you are in edit mode, and it allows you to select from the available range names.

Inserting functions into formulas

The easiest way to enter a function into a formula is to use Formula AutoComplete (the drop-down list that Excel displays while you type a formula). 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 tools in the Function Library group on the Formulas tab on the Ribbon (see Figure 9.3). This method is especially useful if you can't remember which function you need. When entering a formula, click the function category (Financial, Logical, Text, and so on) to get a list of the functions in that category. Click the function that 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.

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

• Choose Formulas ⇨ Function Library ⇨ Insert Function.
• Use the Insert Function command, which appears at the bottom of each drop-down list in the Formulas ⇨ Function Library group.
• Click the Insert Function icon, which is directly to the left of the Formula bar. This button displays fx.
• Press Shift+F3.

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 recently used, 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 field at the top of the dialog box.

1. Enter your search terms and click Go. You get a list of relevant functions. When you select a function from the Select a Function list, Excel displays the function (and its argument names) in the dialog box along with a brief description of what the function does.
2. 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 9.5.

   

3. Specify the arguments for the function. The Function Arguments dialog box will vary, depending on the function that you are 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.
4. After you specify all of the function arguments, click OK.

Function entry tips

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

• You can use the Insert Function dialog box to insert a function into an existing formula. Just edit the formula and move the insertion point to the location at which you want to insert the function. Then open the Insert Function dialog box (using any of the methods described earlier) and select the function.
• You can also use the Function Arguments dialog box to modify the arguments for a function in an existing formula. Click the function in the Formula bar and then click the Insert Function button (the fx button, to the left of the Formula bar).
• If you change your mind about entering a function, click the Cancel button.
• The number of boxes that you see in the Function Arguments dialog box depends on the number of arguments used in the function you selected. If a function uses no arguments, you won't see any boxes. If the function uses a variable number of arguments (such as the AVERAGE function), Excel adds a new box every time you enter an optional argument. Note that required arguments appear in bold, while optional arguments are not bold.
• As you provide arguments in the Function Arguments dialog box, the value of each argument is displayed to the right of each box.
• A few functions, such as INDEX, have more than one form. If you choose such a function, Excel displays another dialog box that lets you choose which form you want to use.
• As you become familiar with the functions, you can bypass the Insert Function dialog box and type the function name directly. Excel prompts you with argument names as you enter the function.

Using relative, absolute, and mixed references

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

• Relative    The row and column references can change when you copy the formula to another cell because the references are actually offsets from the current row and column. By default, Excel creates relative cell references in formulas.
• Absolute    The row and column references don't change when you copy the formula because the reference is to an actual cell address. 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).
• Mixed    Either the row or the column reference is relative, and the other is absolute. Only one of the address parts is absolute (for example, $A4 or A$4).

The type of cell reference is important only if you plan to copy the formula to other cells. The following examples illustrate this point.

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

=B2*C2 

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 the same as the formula in cell D2.

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

=(B2*C2)*$B$7 

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 you want because the address of the cell that contains the sales tax never changes.

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

=$B3*C$2 

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.

Changing the types of your references

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.

Referencing cells outside the worksheet

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.

Summarizing data in a table

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

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:

1. Activate any cell in the table.
2. Place a check mark next to Table Tools Design ⇨ Table Style Options ⇨ Total Row. Excel adds a total row to the table and displays the sum of each numeric column.
3. To change the type of summary formula, activate a cell in the total row and use the drop-down list to change the type of summary formula to use (see Figure 9.10). For example, to calculate the average of the Actual column, select AVERAGE from the drop-down list in cell D15. Excel creates this formula:

=SUBTOTAL(101,[Actual]) 

For the SUBTOTAL function, 101 is an enumerated argument that represents AVERAGE. The second argument for the SUBTOTAL function is the column name in square brackets. Using the column name within brackets creates “structured” references within a table. (We discuss this further in the upcoming section “Referencing data in a table.”)

Using formulas within a table

In many cases, you'll want to use formulas within a table to perform calculations that use other columns in the table. For example, in the table shown in Figure 9.10, you may want a column that shows the difference between the Actual and Projected amounts. To add this formula, follow these steps:

1. Activate cell E2 and type Difference for the column header. After you press Enter, Excel automatically expands the table for you to include the new column.
2. Move to cell E3 and type an equal sign to signify the beginning of a formula.
3. Press the left arrow key. Excel displays [@Actual], which is the column heading, in the Formula bar.
4. Type a minus sign and then press the left arrow key twice. Excel displays [@Projected] in your formula.
5. Press Enter to end the formula. Excel copies the formula to all rows in the table.

Figure 9.11 shows the table with the new column.

Examine the table, and you will 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 of the cells in that column. If you need to edit the formula, Excel will automatically copy the edited formula to the other cells in the column.

These steps use the pointing technique to create the formula. Alternatively, you could have entered the formula manually using standard cell references rather than column headers. 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 instead of cell references—they're much easier to understand.

Referencing data in a table

Excel offers some other ways to refer to data that's contained in a table by using the table name and column headers.

You can, of course, use standard cell references to refer to data in a table, but using the table name and column headers has a distinct advantage: the names adjust automatically if the table size changes by adding or deleting rows. In addition, formulas that use table names and column headers will adjust automatically if you change the name of the table or give a new name to a column.

Refer to the table shown in Figure 9.11. This table is named Table1. To calculate the sum of all of the data in the table, enter this formula into a cell outside the table:

=SUM(Table1) 

This formula will return the sum of all of the data (excluding calculated total row values, if any), 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 AnnualData (by using the Name Manager or by choosing Table Tools Design ⇨ Properties ⇨ Table Name), the preceding formula would change to

=SUM(AnnualData) 

Most of the time, a formula will 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 9.12 shows the formula AutoComplete helping to create a formula by showing a list of the elements in the table. Notice that, in addition to the column headers in the table, Excel lists other table elements that you can reference: #All, #Data, #Headers, #Totals, and @ - This Row.

Handling circular references

When you're entering formulas, you may occasionally see a warning message indicating that the formula you just entered will result in a circular reference. A circular reference occurs when a formula refers to its own cell—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.

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

• Click OK to enter the formula as is.
• Click Help to see a help screen about circular references.

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

Often, a circular reference is quite obvious and 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 to the original formula), it may require a bit of detective work to get to the problem.

Specifying when formulas are calculated

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 of 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:

• When you make a change—enter or edit data or formulas, for example—Excel calculates immediately those formulas that depend on new or edited data.
• If Excel is in the middle of a lengthy calculation, it temporarily suspends the calculation when you need to perform other worksheet tasks; it resumes calculating when you're finished with your other worksheet tasks.
• Formulas are evaluated in a natural sequence. In other words, if a formula in cell D12 depends on the result of a formula in cell D24, Excel calculates cell D24 before calculating cell D12.

Sometimes, however, you may want to control when Excel calculates formulas. For example, if you create a worksheet with thousands of complex formulas, you may 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.

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:

• F9: Calculates the formulas in all open workbooks.
• Shift+F9: Calculates only the formulas in the active worksheet. Other worksheets in the same workbook aren't calculated.
• Ctrl+Alt+F9: Forces a complete recalculation of all formulas.
• Ctrl+Alt+Shift+F9: Rebuilds the calculation dependency tree and performs a complete recalculation.

Using names for constants

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:

1. Choose Formulas ⇨ Defined Names ⇨ Define Name. The New Name dialog box appears.
2. Enter the name (in this case, SalesTax) into the Name field.
3. Select a scope in which the name will be valid (either the entire workbook or a specific worksheet).
4. Click the Refers To text box, delete its contents, and replace the old contents with a value (such as .075).
5. (Optional) Use the Comment box to provide a comment about the name.
6. Click OK to close the New Name dialog box and create the name.

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 can also use this constant in a formula, such as =A1*SalesTax.

Using names for formulas

In addition to creating named constants, you can create named formulas. Like a named constant, a named formula doesn't reside in a cell.

You create named formulas the same way that 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 9.13 shows an example. In this case, the name MonthlyRate refers to the following formula:

=Sheet3!$B$1/12 

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 field 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 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, cell D12 displays the contents of cell C12 raised to the third power. (C12 is the cell directly to the left of cell D12.)

Using range intersections

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 9.14 shows a simple example.

The formula in cell B9 is

=C1:C6 A3:D3 

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

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

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

Excel created the following eight 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(Quarter4) 

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

=Quarter1 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 Quarter1 range intersects with the West range. Naming ranges in this manner can help you create very readable formulas.

Applying names to existing references

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 later 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. The Apply Names dialog box appears. Select the names you want to apply by clicking them and then click OK. Excel replaces the range references with the names in the selected cells.

Not hard-coding values

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%), 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%, 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.

Using the Formula bar as a calculator

If you need to perform a quick 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.)

Making an exact copy of a formula

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:

1. Double-click A1 (or press F2) to get into Edit mode.
2. Drag the mouse to select the entire formula. You can drag from left to right or from right to left. To select the entire formula with the keyboard, press End, followed by Shift+Home.
3. Choose Home ⇨ Clipboard ⇨ Copy (or press Ctrl+C). This copies the selected text (which will become the copied formula) to the Clipboard.
4. Press Esc to leave Edit mode.
5. Select cell A2.
6. Choose Home ⇨ Clipboard ⇨ Paste (or press Ctrl+V) to paste the text into cell A2.

You can also 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're pasted into a new cell. That's because the formulas are being copied as text, not as actual formulas.

Converting formulas to values

In certain scenarios, you may want to use formulas to get to an answer and then convert those formulas to actual values. 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:

1. Select A1:A20.
2. Choose Home ⇨ Clipboard ⇨ Copy (or press Ctrl+C).
3. Choose Home ⇨ Clipboard ⇨ Paste Values (V).
4. Press Esc to cancel Copy mode.