Chapter 12: Building Formulas

Formulas are the heart and soul of a spreadsheet, and Microsoft Office Excel 2007 offers a rich environment in which to build complex formulas. Armed with a few mathematical operators and rules for cell entry, you can turn a worksheet into a powerful calculator. In this chapter, we'll cover the basics, and then we'll look closer at using functions, defining names, building structured references, working with arrays, creating linking formulas, and constructing conditional tests.

Formula Fundamentals

All formulas in Excel begin with an equal sign. The equal sign tells Excel that the succeeding characters constitute a formula. If you omit the equal sign, Excel might interpret the entry as text.

To show how formulas work, we'll walk you through some rudimentary ones. Begin by selecting blank cell A10. Then type =10+5, and press Enter. The value 15 appears in cell A10. Now select cell A10, and the formula bar displays the formula you just typed. What appears in the cell is the displayed value; what appears in the formula bar is the underlying value, which in this case is a formula.

Understanding the Precedence of Operators

Operators are symbols that represent specific mathematical operations, including the plus sign (+), minus sign (-), division sign (/), and multiplication sign (*). When performing these operations in a formula, Excel follows certain rules of precedence:

• Excel processes expressions within parentheses first.

• Excel performs multiplication and division before addition and subtraction.

• Excel calculates consecutive operators with the same level of precedence from left to right.

Type some formulas to see how these rules apply. Select an empty cell, and type =4+12/6. Press Enter, and you see the value 6. Excel first divides 12 by 6 and then adds the result (2) to 4. If Excel used different precedence rules, the result would be different. For example, select another empty cell, and type =(4+12)/6. Press Enter, and you see the value 2.666667. This demonstrates how you can change the order of precedence using parentheses. The formulas in Table 12-1 contain the same values and operators, but note the different results because of the placement of parentheses.

Table 12-1: Placement of Parentheses
Open table as spreadsheet

Formula	Result
= 3*6+12/4-2	19
=(3*6)+12/(4-2)	24
= 3*(6 + 12)/4-2	11.5
=(3*6 + 12)/4-2	5.5
= 3*(6 + 12/(4-2))	36

If you do not include a closing parenthesis for each opening parenthesis in a formula, Excel displays the message "Microsoft Excel found an error in this formula" and provides a suggested solution. If the suggestion matches what you had in mind, simply press Enter, and Excel completes the formula for you.

When you type a closing parenthesis, Excel briefly displays the pair of parentheses in bold. This feature is handy when you are typing a long formula and are not sure which pairs of parentheses go together.

Note 

If you are unsure of the order in which Excel will process a sequence of operators, use parentheses-even if the parentheses aren't necessary. Parentheses also make your formulas easier to read and interpret, which is helpful if you or someone else needs to change them later.

Using Cell References in Formulas

A cell reference identifies a cell or group of cells in a workbook. When you include cell references in a formula, the formula is said to be linked to the referenced cells. The resulting value of the formula depends on the values in the referenced cells and changes automatically when the values in the referenced cells change.

To see cell referencing at work, select cell A1, and type the formula =10*2. Now select cell A2, and type the formula =A1. The value in both cells is 20. If at any time you change the value in cell A1, the value in cell A2 changes also. Now select cell A3, and type =A1+A2. Excel returns the value 40. Cell references are especially helpful when you create complex formulas.

Entering Cell References by Clicking

You can save time and increase accuracy when you enter cell references in a formula by selecting them with your pointer. For example, to enter references to cells A9 and A10 in a formula in cell B10, do the following:

1. Select cell B10, and type an equal sign.

2. Click cell A9, and type a plus sign.

3. Click cell A10, and press Enter.

When you click each cell, a marquee surrounds the cell, and Excel inserts a reference to the cell in cell B10. After you finish entering a formula, be sure to press Enter. If you do not press Enter and then select another cell, Excel assumes you want to include the cell reference in the formula.

The active cell does not have to be visible in the current window for you to enter a value in that cell. You can scroll through the worksheet without changing the active cell and click cells in remote areas of your worksheet, in other worksheets, or in other workbooks, as you build a formula. The formula bar displays the contents of the active cell, no matter which area of the worksheet is currently visible.

Note 

If you scroll through your worksheet and the active cell is no longer visible, you can redisplay it by pressing Ctrl+Backspace. You can return to the upper-left corner of the worksheet by pressing Ctrl+Home.

Understanding Relative, Absolute, and Mixed References

Relative references-the type we've used so far in the sample formulas-refer to cells by their position in relation to the cell that contains the formula, such as "the cell two rows above this cell." Absolute references refer to cells by their fixed position in the worksheet, such as "the cell located at the intersection of column A and row 2." A mixed reference contains a relative reference and an absolute reference, such as "the cell located in column A and two rows above this cell." Absolute and mixed references are important when you begin copying formulas from one location to another in your worksheet. When you copy and paste, relative references adjust automatically, while absolute references do not. For information about copying cell references, see "How Copying Affects Cell References" on page 432.

A relative reference to cell A1, for example, looks like this: =A1. An absolute reference to cell A1 looks like this: =$A$1. You can combine relative and absolute references to cell A1 to create these mixed references: =$A1 or =A$1.

If the dollar sign precedes only the letter (A, for example), the column coordinate is absolute, and the row is relative. If the dollar sign precedes only the number (1, for example), the column coordinate is relative, and the row is absolute.

While entering or editing a formula, press F4 to change reference types quickly. The following steps show how:

1. Select cell A1, and type =B1+B2 (but do not press Enter).

2. Press F4 to change the reference nearest to the flashing cursor to absolute. The formula becomes =B1+$B$2.

3. Press F4 again to change the reference to mixed (relative column coordinate and absolute row coordinate). The formula becomes =B1+B$2.

4. Press F4 again to reverse the mixed reference (absolute column coordinate and relative row coordinate). The formula becomes =B1+$B2.

5. Press F4 again to return to the original relative reference.

When you use this technique to change reference types, activate the formula bar by clicking it, and then you can either click in the cell reference you want to change before pressing F4 or drag to select one or more cell references in the formula and change all the selected references at the same time.

Creating References to Other Worksheets in the Same Workbook

You can refer to cells in other worksheets within the same workbook just as easily as you refer to cells in the same worksheet. For example, to enter a reference to cell A9 in Sheet2 into cell B10 in Sheet1, do this:

1. Select cell B10 in Sheet1, and type an equal sign.

2. Click the Sheet2 tab.

3. Click cell A9, and then press Enter.

After you press Enter, Sheet1 becomes active. Select cell B10, and you will see that it contains the formula =Sheet2!A9.

The worksheet portion of the reference is separated from the cell portion by an exclamation point. Note also that the cell reference is relative, which is the default when you select cells to create references to other worksheets.

Creating References to Worksheets in Other Workbooks

You can refer to cells in worksheets in separate workbooks in the same way you refer to cells in other worksheets within the same workbook. These references are called external references. For example, to enter a reference to Book2 in Book1, follow these steps:

1. Create a new workbook-Book2-by clicking the Microsoft Office Button, clicking New, selecting Blank Workbook, and clicking OK.

2. Click the View tab, click Arrange All, select the Vertical option, and click OK.

3. Select cell A1 in Sheet1 of Book1, and type an equal sign.

4. Click anywhere in the Book2 window to make the workbook active.

5. Click the Sheet2 tab at the bottom of the Book2 window.

6. Click cell A2. Before pressing Enter to lock in the formula, your screen should look similar to Figure 12-1.

7. Press Enter to lock in the reference.

Figure 12-1: Enter external references easily by clicking the cell to which you want to refer.

Understanding Row-Column Reference Style

In the regular A1 reference style, rows are numbered, and columns are designated by letters. In R1C1 reference style, both rows and columns are numbered. The cell reference R1C1 means row 1, column 1; therefore, R1C1 and A1 refer to the same cell. Although R1C1 reference style isn't widely used anymore, it was the standard in some "classic" spreadsheet programs, such as Microsoft Multiplan.

To turn on the R1C1 reference style, click the Microsoft Office Button, click Excel Options, select the Formulas category, select the R1C1 Reference Style check box, and then click OK. The column headers change from letters to numbers, and the cell references in all your formulas automatically change to R1C1 format. For example, cell M10 becomes R10C13, and cell XFD1048576, the last cell in your worksheet, becomes R1048576C16384.

In R1C1 notation, a relative cell reference displays in terms of its relationship to the cell that contains the formula rather than by its actual coordinates. This can be helpful when you are more interested in the relative position of a cell than in its absolute position. For example, suppose you want to enter in cell R10C2 (B10) a formula that adds cells R1C1 (A1) and R1C2 (B1). After selecting cell R10C2, type an equal sign, select cell R1C1, type a plus sign, select cell R1C2, and then press Enter. When you select cell R10C2, the formula =R[-9]C[-1]+R[-9]C appears in the formula bar. Negative row and column numbers indicate that the referenced cell is above or to the left of the formula cell; positive numbers indicate that the referenced cell is below or to the right of the formula cell. The brackets indicate relative references. This formula reads, "Add the cell nine rows up and one column to the left to the cell nine rows up in the same column."

A relative reference to another cell must include brackets. Otherwise, Excel assumes you're using absolute references. For example, if we select the entire formula we created in the previous paragraph in the formula bar and press F4, the formula changes to =R1C1+R1C2 using absolute references.

How Copying Affects Cell References

One of the handiest benefits of using references is the capability to copy and paste formulas. But you need to understand what happens to your references after you paste so you can create formulas with references that operate the way you want them to operate.

Copying Relative References When you copy a cell containing a formula with relative cell references, Excel changes the references automatically, relative to the position of the cell where you paste the formula. Referring to Figure 12-2, suppose you type the formula =AVERAGE(B4:E4) in cell F4. This formula averages the values in the four-cell range that begins four columns to the left of cell F4.

Figure 12-2: Cell F4 contains relative references to the cells to its left.

On the CD You'll find the  Exams.xlsx file in the Sample Files section of the companion CD.

You want to repeat this calculation for the remaining rows as well. Instead of typing a new formula in each cell in column F, you select cell F4 and press Ctrl+C to copy it (or click the Copy button in the Clipboard group on the Home tab). Then you select cells F5:F8, click the arrow next to the Paste button on the Home tab, click Paste Special, and then select the Formulas And Number Formats option (to preserve the cell and border formatting). Figure 12-3 shows the results. Because the formula in cell F4 contains a relative reference, Excel adjusts the references in each copy of the formula. As a result, each copy of the formula calculates the average of the cells in the corresponding row. For example, cell F5 contains the formula =AVERAGE(B5:E5).

Figure 12-3: We copied the relative references from cell F4 to cells F5:F8.

Copying Absolute References If you want cell references to remain the same when you copy them, use absolute references. For example, in the worksheet on the left in Figure 12-4, cell B2 contains the hourly rate at which employees are to be paid, and cell C5 contains the relative reference formula =B2*B5. Suppose you want to copy the formula in C5 to the range C6:C8. The worksheet on the right in Figure 12-4 shows what happens if you copy the existing formula to this range: You get erroneous results. Although the formulas in cells C6:C8 should refer to cell B2, they don't. For example, cell C8 contains the incorrect formula =B5*B8.

Figure 12-4: The formula in cell C5 contains relative references. We copied the relative formula in cell C5 to cells C6:C8, producing incorrect results.

On the CD You'll find the  Wages.xlsx file in the Sample Files section of the companion CD.

Because the reference to cell B2 in the original formula is relative, it changes as you copy the formula to the other cells. To correctly apply the wage rate in cell B2 to all the calculations, you must change the reference to cell B2 to an absolute reference before you copy the formula.

To change the reference style, click the formula bar, click the reference to cell B2, and then press F4. The result is the following formula: =$B$2*B5.

When you copy this modified formula to cells C6:C8, Excel adjusts the second cell reference, but not the first, within each formula. In Figure 12-5, cell C8 now contains the correct formula: =$B$2*B8.

Figure 12-5: We created an absolute reference to cell B2 before copying the formula.

Copying Mixed References You can use mixed references in your formulas to anchor a portion of a cell reference. (In a mixed reference, one portion is absolute, and the other is relative.) When you copy a mixed reference, Excel anchors the absolute portion and adjusts the relative portion to reflect the location of the cell to which you copied the formula.

To create a mixed reference, you can press the F4 key to cycle through the four combinations of absolute and relative references-for example, from B2 to $B$2 to B$2 to $B2.

The loan payment table in Figure 12-6 uses mixed references (and an absolute reference). You need to enter only one formula in cell C6 and then copy it down and across to fill the table. Cell C6 contains the formula = -PMT($B6,$C$3,C$5) to calculate the annual payments on a $10,000 loan over a period of 15 years at an interest rate of 6 percent. We copied this formula to cells C6:F10 to calculate payments on three additional loan amounts using four additional interest rates.

Figure 12-6: This loan payment table uses formulas that contain mixed references.

On the CD You'll find the  Loan.xlsx file in the Sample Files section of the companion CD.

The first cell reference, $B6, indicates we always want to refer to the values in column B but the row reference (Rate) can change. Similarly, the mixed reference, C$5, indicates we always want to refer to the values in row 5 but the column reference (Loan Amount) can change. For example, cell E8 contains the formula = -PMT($B8,$C$3,E$5). Without mixed references, we would have to edit the formulas manually in each of the cells in the range C6:F10.

TROUBLESHOOTING 

Inserted cells are not included in formulas.

If you have a SUM formula at the bottom of a row of numbers and then insert new rows between the numbers and the formula, the range reference in the SUM function doesn't include the new cells. Unfortunately, you can't do much about this. This is an age-old worksheet problem, but Excel attempts to correct it for you automatically. Although the range reference in the SUM formula will not change when you insert the new rows, it will adjust as you type new values in the inserted cells. The only caveat is that you must enter the new values one at a time, starting with the cell directly below the column of numbers. If you begin entering values in the middle of a group of newly inserted rows or columns, the range reference remains unaffected. For more information about the SUM function, see "Using the SUM Function" on page 497.

Editing Formulas

You edit formulas the same way you edit text entries. To delete characters in a formula, drag through the characters in the cell or the formula bar, and press Backspace or Delete. To replace a character, highlight it, and type its replacement. To replace a reference, highlight it, and click the new cell you want the formula to use; Excel enters a relative reference automatically. You can also insert additional cell references in a formula. For example, to insert a reference to cell B1 in the formula =A1+A3, simply move the insertion point between A1 and the plus sign, and either type +B1 or type a plus sign and click cell B1. The formula becomes =A1+B1+A3.

Understanding Reference Syntax

So far, we have used the default worksheet and workbook names for the examples in this book. When you save a workbook, you must give it a permanent name. If you create a formula first and then save the workbook with a new name, Excel adjusts the formula accordingly. For example, if you save Book2 as Sales.xlsx, Excel changes the remote reference formula =[Book2]Sheet2!$A$2 to =[Sales.xlsx]Sheet2!$A$2. And if you rename Sheet2 of Sales.xlsx to February, Excel changes the reference to =[Sales.xlsx]February!$A$2. If the referenced workbook is closed, Excel displays the full path to the folder where the workbook is stored in the reference, as shown in the example ='C:\Work\[Sales.xlsx]February'!$A$2.

In the preceding example, note that apostrophes surround the workbook and worksheet portion of the reference. Excel adds the apostrophes around the path when you close the workbook. If you type a new reference to a closed workbook, however, you must add the apostrophes yourself. To avoid typing errors, open the closed workbook, and click cells with your cursor to enter references so that Excel inserts them in the correct syntax for you.

Using Numeric Text in Formulas

The seemingly oxymoronic term numeric text refers to an entry that is not strictly numbers but includes both numbers and a few specific text characters. You can perform mathematical operations on numeric text values as long as the numeric string contains only the following characters:

 0 1 2 3 4 5 6 7 8 9 . + - E e 

In addition, you can use the / character in fractions. You can also use the following five number-formatting characters:

 $ , % ( ) 

You must enclose numeric text strings in quotation marks. For example, if you type the formula =$1234+$123, Excel displays an error message stating that Excel found an error in the formula you typed. (The error message also offers to correct the error for you by removing the dollar signs.) But the formula ="$1234"+"$123" produces the result 1357 (ignoring the dollar signs). When Excel performs the addition, it automatically translates numeric text entries into numeric values.

For more information about number-formatting characters, see "Formatting As You Type" on page 297.

About Text Values

The term text values refers to any entry that is neither a number nor a numeric text value (see the previous section); Excel treats the entry as text only. You can refer to and manipulate text values using formulas. For example, if cell A1 contains the text First and you type the formula =A1 in cell A10, cell A10 displays First.

For more information about manipulating text with formulas, see "Understanding Text Functions" on page 502.

You can use the & (ampersand) operator to concatenate, or join, several text values. Extending the preceding example, if cell A2 contains the text Quarter and you type the formula =A1&A2 in cell A3, then cell A3 displays FirstQuarter. To include a space between the two strings, change the formula to =A1&" "&A2. This formula uses two concatenation operators and a literal string, or string constant (a space enclosed in quotation marks).

You can use the & operator to concatenate strings of numeric values as well. For example, if cell A3 contains the numeric value 867 and cell A4 contains the numeric value 5309, the formula =A3&A4 produces the string 8675309. This string is left-aligned in the cell because it's considered a text value. (Remember, you can use numeric text values to perform any mathematical operation as long as the numeric string contains only the numeric characters listed at the top of this page.)

Finally, you can use the & operator to concatenate a text value and a numeric value. For example, if cell A1 contains the text January and cell A3 contains the numeric value 2009, the formula =A1&A3 produces the string January2009.

Practical Concatenation

Depending on the kind of work you do, the text manipulation prowess of Excel may turn out to be the most important skill you learn in this book. If you deal with a lot of mailing lists, for example, you probably use a word-processing application such as Microsoft Office Word 2007. But read on-you might find that Excel has the tools you've been wishing for, and it just might become your text manipulation application of choice.

Suppose you have a database of names in which the first and last names are stored in separate columns. This example shows you how to generate a list of full names:

We created the full names listed in columns D and E using formulas like the one visible in the formula bar. For example, the formula in cell D2 is =A2&" "&B2, which concatenates the contents of the cells in columns A and B and adds a space character in between. The formula in cell E2 (shown in the illustration) reverses the position of the first and last names and adds a comma before the space character.

Pretty soon, you'll be using the term concatenate in everyday conversation. Instead of the old "ducks in a row" metaphor, you'll be saying, "We must concatenate our ducks." Caution is advised.

On the CD You'll find the  Concatenation.xlsx file in the Sample Files section of the companion CD.

Understanding Error Values

An error value is the result of a formula that Excel can't resolve. Table 12-2 describes the seven error values.

Table 12-2: Error Values
Open table as spreadsheet

Error Value	Cause
#DIV/0!	You attempted to divide a number by zero. This error usually occurs when you create a formula with a divisor that refers to a blank cell.
#NAME?	You typed a name that doesn't exist in a formula. You might have mistyped the name or typed a deleted name. Excel also displays this error value if you do not enclose a text string in quotation marks.
#VALUE	You entered a mathematical formula that refers to a text entry.
#REF!	You deleted a range of cells whose references are included in a formula.
#N/A	No information is available for the calculation you want to perform. When building a model, you can type #N/A in a cell to show you are awaiting data. Any formulas that reference cells containing the #N/A value return #N/A.
#NUM!	You provided an invalid argument to a worksheet function. #NUM! can indicate also that the result of a formula is too large or too small to be represented in the worksheet.
#NULL!	You included a space between two ranges in a formula to indicate an intersection, but the ranges have no common cells.