PART II Calculate, Manipulate, and Analyze Data
CHAPTER 8 Create Formulas to Perform Custom Calculations
Color profile: Generic CMYK printer profile Composite Default screen
How to…
■ Understand formula components
■ Understand how Excel handles numbers
■ Refer to cells, ranges, other worksheets, and other workbooks in formulas
■ Enter a sample formula
■ Use range names and labels in formulas
■ Use absolute, relative, and mixed references in formulas
■ Work with array formulas
■ Display formulas in worksheets—or hide formulas from other users
■ Troubleshoot formulas
In Chapter 7, you learned how to enter Excel’s built-in functions to perform calculations. Excel’s functions are great for performing a wide variety of standard calculations—as you saw, the functions encompass everything from adding a series of values to testing the logical truth or falsity of conditions to manipulating statistics and text. But often you’ll need to perform calculations that the built-in functions don’t cover. For such calculations, you create custom formulas.
This chapter describes the basics of formulas in Excel and the components from which formulas are constructed. Then it covers how Excel handles numbers and how to create both regular formulas and array formulas. Finally, you’ll learn how to troubleshoot formulas when they go wrong.
Understand Formula Components
Aformulais a set of instructions for performing a calculation. Excel enables you to create formulas for performing whatever types of calculations you need. In a formula, you use operands to tell Excel which items to use and operators to specify which operation or operations to perform on them.
A formula can contain up to seven nested functions—enough to enable you to perform highly complex calculations.
Each formula begins with an equal sign, so the standard way of starting to enter a formula is to type an equal sign. However, Excel automatically enters the equal sign if you type + or – at the start of a formula, so you don’t always need to type it.
Operands
Theoperandsin a formula specify the data you want to calculate. An operand can be:
■ A constant value you enter in the formula itself (for example,=8*12) or in a cell (for example,=B1*8)
8
■ A cell address, range address, or range name
■ A worksheet function
Operators
Theoperatorsin a formula specify the operation you want to perform on the operands. Excel uses arithmetic operators, logical operators, reference operators, and one text operator. Table 8-1 explains these operators.
CHAPTER 8: Create Formulas to Perform Custom Calculations 167
HowTo-Tght (8)/ How to Do Everything with Microsoft Office Excel 2003 / Hart-Davis / 3071-1 / Chapter 8
Operator Explanation
+ Addition
– Subtraction
* Multiplication
/ Division
% Percent
^ Exponentiation
= Equal to
<> Not equal to
> Greater than
>= Greater than or equal to
< Less than
<= Less than or equal to
: Range of contiguous cells (for example,A1:C16) , Range of noncontiguous cells (for example,A1,B2)
[space] The cell or range shared by two references. For example,=SUM(B1:B10 A5:D6)adds the contents of cells B5 and B6 because these cells are at the intersection of the ranges B1:B10 and A5:D6.
& Concatenates (joins) the specified values. For example, if cell A1 contains 50 and cell A2 contains 50, the formula=A1&A2returns5050—the cell contents joined together rather than added together.
Color profile: Generic CMYK printer profile Composite Default screen
Understand and Change Operator Precedence
When a formula contains only one operator, you don’t have to worry about the order in which Excel handles operators. But as soon as you create a formula with two or more different operators, you need to know the order in which they’ll be evaluated. For example, consider the formula
=1000-100*5. Does Excel subtract 100 from 1000 and multiply the result (900) by 5, giving 4500? Or does Excel multiply 100 by 5 and subtract the result (500) from 1000, giving 500?
As you can see, the same calculation gives quite different results depending on the order in which its operations are performed.
In the example, Excel multiplies 100 by 5 and subtracts 500 from 1000 using its default settings, so the result is 500. Table 8-2 shows the order ofoperator precedence—the order in which Excel evaluates the operators—in descending order. When a formula uses two operators that share a precedence, Excel evaluates the operators from left to right, in the same direction as you read.
You can change operator precedence in a formula by using parentheses to indicate which items you want to calculate first. For example, to evaluate the formula=1000-100*5the other way, enter=(1000-100)*5. Excel would subtract 100 from 1000 and then multiply the result (900) by 5.
When you nest multiple items, Excel evaluates the most deeply nested item first. For example, in the formula=(100-(10*5))/20, Excel evaluates 10*5 first, because that item is nested within two sets of parentheses. You can use many levels of nested parentheses if necessary.
If you find it hard to remember the order of operator precedence, you can use parentheses even when they’re not strictly necessary.
When you’re editing a formula, Excel displays differently nested parentheses in different colors to help you keep track of which parenthesis is paired with which. When you use←and→ to move through a formula that you’re editing in the active cell or in the Formula bar, Excel flashes the paired parenthesis for each parenthesis you move over. If you omit a parenthesis in a formula, Excel does its best to warn you of the problem and identify where the missing parenthesis should go.
Operator Explanation
– Negation (negative numbers)
% Percentage
^ Exponentiation
*, / Multiplication, division
+, – Addition, subtraction
& Concatenation
=, <>, <, <=, >, >= Comparison operators
TABLE 8-2 Operator Precedence in Descending Order
8
CHAPTER 8: Create Formulas to Perform Custom Calculations 169
HowTo-Tght (8)/ How to Do Everything with Microsoft Office Excel 2003 / Hart-Davis / 3071-1 / Chapter 8
Control Excel’s Automatic Calculation
As discussed in “Understand (and Maybe Choose) Calculation Options” in Chapter 2, Excel’s default setting is to automatically calculate all formulas all the time. If you’re using a worksheet or workbook with enough data and complex calculations to slow down your computer while you’re entering data in the workbook, you may prefer to turn off automatic calculation. To do so, choose Tools | Options, and then click the Calculation tab. Select the Manual option button, make sure the Recalculate Before Save check box is selected, and then click the OK button.
If you do turn off automatic calculation, Excel displaysCalculatein the status bar when the workbook contains uncalculated calculations. You can force calculation manually for the active worksheet by pressingCTRL-F9and for the entire active workbook by pressingF9. (Alternatively, display the Calculation tab of the Options dialog box and click the Calc Now button or Calc Sheet button.)
Understand How Excel Handles Numbers
Numbers in Excel aren’t necessarily as precise as they appear to be. To avoid running into avoidable errors in calculations, you should understand how Excel handles numbers.
The key limitation is that numbers in Excel can be up to 15 digits long. Those 15 digits can appear on either side of the decimal point—for example, 123456789012345, 1234567.89012345, or .123456789012345. Excel changes all digits beyond the 15th to 0. So if you enter
1234567890123456, Excel actually uses 1234567890123450. For very precise calculations, this truncation can cause problems.
You can format Excel to display up to 30 decimal places, but there’s no reason to do so.
Refer to Cells and Ranges in Formulas
To refer to a cell or a range in a formula, enter its address either by typing or by using the mouse.
Enter Complex Formulas More Easily
Even with Excel’s help, formulas with many deeply nested items can be confusing to enter and difficult to troubleshoot when they don’t produce the results that you expect.
If math isn’t your forte, you may prefer to break a complex calculation down into a sequence of steps that you perform in separate cells. That way, you can trace the steps of the calculation more easily. And you can hide the rows or columns that contain the cells (or use a hidden worksheet) if you prefer not to let other people see them.
Color profile: Generic CMYK printer profile Composite Default screen
When a formula includes two or more ranges, Excel uses different-colored borders to help you keep them straight.
To refer to a whole column, specify its letter as the beginning and end of the range. For example, to make column K reflect the contents of column C, click the column heading for column K, enter=C:C, and pressCTRL-ENTERto enter the formula in all the cells of the selected column.
Similarly, to refer to a whole row, specify its number as the beginning and end of the range: for example,4:4. To refer to a set of columns, specify the beginning and ending letters: for example, A:D. To refer to a set of rows, specify the beginning and ending numbers: for example,1:2.
Refer to Other Worksheets and Other Workbooks in Formulas
To refer to another worksheet in the same workbook in a formula, enter the worksheet name (in single quotes if the name includes one or more spaces) and an exclamation point (!) before the cell address or range address. You can type the name if you choose, but most people find it easier to click the worksheet tab and select the cell or range with the mouse. That way, Excel enters the details automatically for you, including single quotes if they’re necessary.
This example refers to cell F34 on the worksheet named Computer Equipment:
='Computer Equipment'!F34
This example refers to cell J17 on the worksheet named Software:
=Software!J17
If you rename a worksheet, Excel automatically changes the sheet name in all formulas that reference the worksheet.
A formula can also refer to a worksheet in another workbook, but you need to be careful not to move the referenced workbook—if you do, the formula will stop working unless you change the formula to point to the correct location.
To refer to another workbook, enter the workbook path and filename in brackets ([]) followed by the worksheet name. You can type the reference manually, but the easiest way to enter a reference to a worksheet in another workbook is by opening the workbook. Follow these steps:
1. Open the source workbook (the workbook to which you want to create the reference).
2. Start the formula in the destination workbook.
3. Use the Window menu to switch to the source workbook.
4. Select the appropriate worksheet and cell.
5. Use the Window menu to switch back to the destination workbook.
6. Complete the formula. Excel enters the reference automatically for you.
8
CHAPTER 8: Create Formulas to Perform Custom Calculations 171
HowTo-Tght (8)/ How to Do Everything with Microsoft Office Excel 2003 / Hart-Davis / 3071-1 / Chapter 8
Try Entering a Formula
For practice, try entering a formula. Follow these steps:
1. Enter2000in cell A1,4000in cell A2, and2in cell B1.
2. Select cell B2:
3. Type=(.
4. Click cell A1. Excel enters it in the formula:
5. Type+.
6. Click cell A2. Excel enters it in the formula:
7. Type)/.
8. Click cell B1. Excel enters it in the formula:
9. PressENTERor click the Enter button to enter the formula. Excel completes the formula and displays the result in cell B2:
Color profile: Generic CMYK printer profile Composite Default screen
You can quickly copy a formula from one cell to other cells by using the Copy and Paste commands, by usingCTRL-drag and drop, by using the options on the Edit | Fill submenu, or by dragging the AutoFill handle.
Use Range Names and Labels in Formulas
An easy way of referring to a cell or a range is to define a name for it. (“Assign a Name to a Range” in Chapter 1 discusses how to define range names.) You can then use the range name in formulas instead of specifying the cell address or range address. This technique is particularly useful for simplifying the process of referring to cells and ranges on other worksheets in a workbook. For example, instead of using=‘Computer Equipment’!F34 to refer to a cell on another worksheet, you could assign a name to it, and then refer to the name—say,=TotalSales.
If you use range names in formulas, you must be very careful when deleting range names. Otherwise, any formula that references the deleted range name will display a
#NAME? error.
Another method of simplifying the process of entering formulas in worksheets that include row labels and column labels is to use labels to reference cell addresses. Using labels like this can save time and effort, but there are some potential problems you’ll learn about in a minute. So by default this feature is turned off. To turn it on, choose Tools | Options, select the Accept Labels in Formulas check box on the Calculations tab, and click the OK button.
To use labels to denote a reference, you specify the appropriate row label and column label with a space between the two. For example, the following worksheet contains the column labels Shanghai, Rangoon, and Hong Kong, and the row labels January, February, and March:
Cell B5 uses the formula=Shanghai January + ‘Hong Kong’ Januaryto add cell B2 (the intersection of the Shanghai column and the January row) and cell D2 (the intersection of the Hong Kong column and the January row). Similarly, you could enter=AVERAGE(Rangoon)to average the contents of the Rangoon column.
8
CHAPTER 8: Create Formulas to Perform Custom Calculations 173
HowTo-Tght (8)/ How to Do Everything with Microsoft Office Excel 2003 / Hart-Davis / 3071-1 / Chapter 8
As you can see in that example, you must use single quotes around a label that contains one or more spaces so that Excel knows it should treat the label as a unit rather than as an intersection.
Likewise, you must use single quotes around any label that Excel would otherwise mistake for a cell address (for example, FY2004 or I80).
Labels can greatly simplify formulas, and Excel is designed to handle them intelligently. For example, AutoFill can extend formulas that contain labels, and Excel alters formulas appropriately when you copy a formula that contains labels and paste it to a new location. Similarly, when you alter a label, Excel updates all formulas that reference it.
However, you must watch out for these problems:
■ Duplicate labels If you have two or more instances of a label, Excel can get confused as to which you mean.
■ Adding or deleting columns or rows If you add or delete columns or rows at the edge of a range referenced by a formula using a label, Excel may fail to change the formula accordingly.
After adding or deleting columns or rows, it’s best to edit each formula that might be affected and make sure the area it references is correct. Making this extra check can take more time than using labels saves you, so it can be a strong disincentive to using labels.
■ Merging cells When you merge cells, Excel stores their contents in the upper-left merged cell. If you merge row or column headings, you’ll produce #NAME? errors in formulas that use labels referring to merged cells other than the upper-left cell.
Because of these potential problems, you may find it safer not to use labels in formulas. To turn off the use of labels, clear the Accept Labels in Formulas check box on the Calculation tab of the Options dialog box (Tools | Options). When you do this, Excel warns you that it will replace any labels that are used in formulas with cell references so that your formulas will continue to work. Click the Yes button to make this change.
Use Absolute, Relative, and Mixed References in Formulas
Excel distinguishes between three kinds of references for cells and ranges:
■ Anabsolute referencealways refers to the same cell, even when you move or copy the formula to another cell or range. For example, if you enter in cell A1 a formula that contains an absolute reference to cell B1 and then move the formula to cell C1, the reference will still be to cell B1.
■ Arelative referencerefers to a cell’s position relative to the cell that contains the formula. For example, if you enter in cell A1 a formula that contains a relative reference to cell B2, Excel notes that the reference is to one column over and one row down. If you move the formula to cell B1, Excel changes the relative reference to refer to cell C2, because C2 is one column over and one row down from the formula’s new location.
Color profile: Generic CMYK printer profile Composite Default screen
■ Amixed referenceis a mixture of an absolute reference and a relative reference. A mixed reference can be absolute in column and relative in row, or relative in column and absolute in row. When you move a formula that contains a mixed reference, the relative part of the reference changes, while the absolute part stays the same.
To tell Excel whether a reference is absolute, relative, or mixed, use a dollar sign ($). A dollar sign before a column designation means that the column is absolute; no dollar sign means that the column is relative. A dollar sign before a row number means that the row is absolute; no dollar sign means that the row is relative. For example:
■ $A$1 is an absolute reference to cell A1.
■ A1 is a relative reference to cell A1.
■ $A1 is a mixed reference to cell A1 with the column absolute.
■ A$1 is a mixed reference to cell A1 with the row absolute.
Excel’s default setting is to use relative references, so if you need to use absolute references, you must change them. The easiest way to change the reference type is by selecting the reference in a formula and pressingF4to cycle through the options: absolute ($A$1), mixed with absolute row (A$1), mixed with absolute column ($A1), and relative. You can also type the necessary dollar signs manually.
If you move a formula by using cut and paste, Excel doesn’t change any relative references that the formula contains.
Work with Array Formulas
Anarray formulais a formula that works on an array (a range of cells) to perform multiple calculations that generate either a single result or multiple results.
To enter an array formula, create the formula as explained earlier in this chapter, but press CTRL-SHIFT-ENTERinstead ofENTERto enter it. Excel displays braces ({}) around an array formula. Excel enters the braces automatically when you create an array formula. You can’t achieve the same effect by typing the braces manually.
The example spreadsheet in Figure 8-1 tracks vacation hours used by employees. Each employee starts (on an unseen area of the worksheet) with a number of accrued vacation hours.
The worksheet contains details, in date order, of the vacation hours taken by each employee and a running total showing the number of vacation hours each employee has left.
The array formula in cell D8 is {=SUM(IF($B$2:B8=B8,$C$2:C8))}. The formula first compares each of the previous cells in column B to the current cell in column B. If the IF function returns TRUE, the second argument in the formula adds the contents of the corresponding cell in column C to the running total in column D. The effect is to keep a running total of the vacation hours available by employee.