BUILDING FORMULAS


Formulas are the heart and soul of a spreadsheet, and Microsoft Excel offers a rich environment in which to build complex formulas.

Formula Fundamentals:

All formulas in Excel begin with an equal sign. The equal sign tells Excel that the succeeding characters constitute a formula rather than normal text.

To see how formulas work, 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 entered. 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:

Expressions within parentheses are processed first.

Multiplication and division are performed before addition and subtraction.

Consecutive operators with the same level of precedence are calculated from left to right.

Enter 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.

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 entering a long formula and are not sure which pairs of parentheses go together.

Tip: When in doubt, use parentheses:

If you are unsure of the order in which Excel will process a sequence of operators, use parentheseseven if the parentheses arent 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 a group of cells in a workbook. When you include cell references in a formula, the formula is linked to the referenced cells. The resulting value of the formula is dependent 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 with the Mouse:

You can save time and increase accuracy when you enter cell references in a formula by selecting them with the mouse. 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 a reference to the cell is inserted in cell B10. After you finish entering a formula, be sure to press Enter. If you do not press Enter and select another cell, Excel assumes that 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.

Tip: Redisplay the active cell.

If you scroll through your worksheet and the active cell is no longer visible, you can redisplay it by pressing Ctrl+Backspace.

Understanding Relative, Absolute, and Mixed References:

Relative references the type weve 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, for example, the cell located at the intersection of column A and row 2. A mixed reference contains a relative reference and an absolute reference, for example, 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.

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.

When you enter or edit 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.

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 is made 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 that 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 into Book1, follow these steps:

1 Create a new workbookBook2by clicking the New button on the Standard toolbar.
2 Choose Window, Arrange, Vertical.
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. 7 Press Enter to lock in the reference.

Understanding Row-Column Reference Style:

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 isnt widely used anymore, it was the standard in some spreadsheet programs, such as Multiplan. The normal reference style in Excel assigns letters to columns and numbers to rows, such as A1 or Z100. To turn on the R1C1 reference style, choose Tools, Options, click the General tab, and select the R1C1 Reference Style option. The cell references in all your formulas automatically change to R1C1 format. For example, cell M10 becomes R10C13, and cell IV65536, the last cell in your worksheet, becomes R65536C256. In R1C1 notation, relative cell references are displayed in terms of their relationship to the cell that contains the formula rather than by their 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. Excel displays =R[-9]C[-1]+R[-9]C. 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 youre using absolute references. For example, the formula =R8C1+R9C1 uses absolute references to the cells in rows 8 and 9 of column 1.

How Copying Affects Cell References:

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

Copying Relative References:

When you copy a cell containing a formula with relative cell references, the references change automatically, relative to the position of the cell where you paste the formula. 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. You can find the sample file used in this example, Exam.xls. 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 choose Edit, Copy. Then you select cells F5:F8, choose Edit, Paste Special, and then select the Formulas and Number Formats option (to preserve the formatting). 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 F7 contains the formula =AVERAGE(B7:E7).

Copying Absolute References:

If you want cell references to remain the same when you copy them, use absolute references.

For example, if cell B2 contains the hourly rate at which employees are to be paid, and cell C5 contains the relative reference formula =B2*B5. Suppose that you want to copy the formula in C5 to the range C6:C8. Now 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 dont. For example, cell C8 contains the incorrect formula =B5*B8. The problem is that the formula in cell C5 contains relative references. We copied the relative formula in cell C5 to cells C6:C8, producing incorrect results. (You can find the sample file used in this example in Wages.xls.) 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, the second cell reference, but not the first, is adjusted within each formula. For example, cell C8 now contains the correct formula: =$B$2*B8.

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 referencesfor example, from B2 to $B$2 to B$2 to $B2. The loan payment example (Loan.xls) 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.

The first cell reference, $B6, indicates that 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 that 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.

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 then click the new cell you want the formula to use. A relative reference is entered 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, the formula is adjusted accordingly. For example, if you save Book2 as Sales.xls, the remote reference formula =[Book2]Sheet2!$A$2 changes to =[Sales.xls]Sheet2!$A$2. And if you rename Sheet2 of Sales.xls to February, the reference changes to =[Sales.xls]February!$A$2. If the referenced workbook is closed, the full path to the folder where the workbook is stored appears in the reference, as shown in the example ='C:\Work\[Sales.xls]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 the mouse to enter references so that Excel inserts them in the correct syntax for you.

Using Numeric Text in Formulas

The 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 entered. (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.

About Text Values

The term text values refers to any entry that is neither a number nor a numeric text value; the entry is treated as text only. You manipulate text values in the same way that you manipulate numeric values. For example, if cell A1 contains the text ABCDE and you type the formula =A1 in cell A10, cell A10 displays ABCDE.

You can use the & (ampersand) operator to concatenate, or join, several text values.

Extending the preceding example, if cell A2 contains the text FGHIJ and you type the formula =A1&A2 in cell A3, cell A3 displays ABCDEFGHIJ. 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 123 and cell A4 contains the numeric value 456, the formula =A3&A4 produces the string 123456. This string is left aligned in the cell because its 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 on the previous page.)

Finally, you can use the & operator to concatenate a text value and a numeric value. For example, if cell A1 contains the text ABCDE and cell A3 contains the numeric value 123, the formula =A1&A3 produces the string ABCDE123. You can find the sample file used in this example as Concatenation.xls.

Using Functions: A Preview

In simplest terms, a function is a predefined formula. Many Excel functions are shorthand versions of frequently used formulas. For example, the SUM function adds a series of cell values by selecting a range. Compare the formula =A1+A2+A3+A4+A5+A6+A7+A8+A9+A10 with the formula =SUM(A1:A10). The SUM function makes the formula a lot shorter, easier to read, and easier to create.

Some Excel functions perform complex calculations. For example, using the PMT function, you can calculate the payment on a loan at a given interest rate and principal amount. All functions consist of a function name followed by a set of arguments enclosed in parentheses. (In the preceding example, A1:A10 is the argument in the SUM function.) If you omit a closing parenthesis when you enter a function, Excel adds the parenthesis after you press Enter, as long as its obvious where the parenthesis is supposed to go. (Relying on this feature can produce unpredictable results; for accuracy, always double-check your parentheses.)

The AutoSum Button

The SUM function is used more often than any other function. To make this function more accessible, Excel includes on the Standard toolbar an AutoSum button, which inserts the SUM function into a cell. To check out the AutoSum feature:

1 Enter a column of numbers
2 Select the cell below the column of numbers and click the AutoSum button. The button inserts the entire formula for you and suggests a range to sum. (If the AutoSum button does not appear on the Standard Toolbar, click the Toolbar Options arrow on the right end of the toolbar to display a list of hidden buttons. After you click it, the AutoSum button will become visible on the toolbar.)

3 If the suggested range is incorrect, simply drag through the correct range, and press Enter.

The AutoSum button includes a menu that appears when you click the arrow next to the button. You can enter the Average, Count, Max, or Min function almost as easily as you can enter the Sum functionall it takes is an extra click to select the function you want from the menu. Plus, the More Functions command opens the Insert Function dialog box, where you can access any Excel function.

You can click the AutoSum button to enter several SUM functions at one time. For example, if we select cells B9:C9 and then click the AutoSum button, Excel enters SUM formulas in both cells.

Inserting Functions

When you want to use a built-in function, click the Insert Function buttonthe fx button located in the formula bar. (You can also choose Insert, Function.) When you do so, the Insert Function dialog box appears.

Creating Three-Dimensional Formulas

You can use references to perform calculations on cells that span a range of worksheets in a workbook. These are called 3-D, or three-dimensional, references. Suppose you set up 12 worksheets in the same workbookone for each monthwith a year-to-date summary sheet on top. If all the monthly worksheets are laid out identically, you could use 3-D reference formulas to summarize the monthly data on the summary sheet. For example, the formula =SUM(Sheet2:Sheet13!B5) adds all the values in cell B5 on all the worksheets between and including Sheet2 and Sheet13. To construct this three-dimensional formula, follow these steps:

1 In cell B5 of Sheet1, type =SUM(.
2 Click the Sheet2 tab, and select cell B5.
3 Click the tab scrolling button (located to the left of the worksheet tabs) until the Sheet13 tab is visible.
4 Hold down the Shift key and click the Sheet13 tab. All the tabs from Sheet2 through Sheet13 change to white, indicating that they are selected for inclusion in the reference you are constructing.
5 Select cell B5.
6 Type the closing parenthesis, and then press Enter.

You can use the following functions with 3-D references: SUM, MIN, VAR, COUNTA, PRODUCT, VARP, AVERAGE, STDEV, COUNT, MAX, and STDEVP.

Formula-Bar Formatting

You can enter spaces and line breaks in a formula to make it easier to readwithout affecting the calculation of the formula. To enter a line break, press Alt+Enter.

Creating Natural-Language Formulas

You can use labels instead of cell references when you create formulas in worksheet tables. Labels at the top of columns and to the left of rows identify the adjacent cells in the table when you use the labels in a formula. This is called a natural-language formula, which is a fairly accurate description of the way it works, providing you use language that is natural.

Tip: Cross the language barrier

The capability to create natural-language formulas is an option that is normally turned off. To enable this feature, choose Tools, Options; on the Calculation tab, click the Accept Labels In Formulas option. Also, be aware that the "Accept labels in formulas" option is not a global setting, but it is stored with each workbook. So, you must enable the option in each workbook in which you want to use natural language formulas.

Note You can find the sample file used in this example, Regional Sales.xls.

The formulas in rows 9 and 10 might normally contain formulas with range references, such as =SUM(B4:B7). But instead of a range reference, a natural-language formula uses a column label from the table, such as =SUM(Qtr 1). When you use text instead of cell references in formulas, Excel looks for column and row labels that match. If Excel finds a matching label, it extrapolates what you are after, using a complex set of internal rules. For our purposes, we can say the following:

If a formula contains a label from the same column or row where the formula resides, Excel assumes that you want to use the entire contiguous range of entries adjacent to the label (either below a column label or to the right of a row label).

If a formula contains a label from a column or row other than the one where the formula resides, Excel assumes that you want to act on a single cell at the intersection of the labeled column or row and the column or row containing the formula.

If what you intend is not clear, Excel displays a dialog box asking you to select the label.

To identify a specific cell in a natural-language formula, we use intersection. For example, the formula =Region 2 Qtr 2 pinpoints the cell located at the intersection of the Region 2 row and the Qtr 2 column, which iscell C5. (Note that you must enter a space between the row and column label in the formula.) If Excel cant tell which label you want to use, the Identify Label dialog box appears.

You can copy and paste natural-language formulas; the columns and rows to which you refer adjust accordingly. In Figure 12-11, the formula in cell B9 was copied to cells C9:E9. All of the formulas in the copy range adjusted so that the label for the current column was used. For example, after copying the formula in cell B9 to the right, the formula in cell E9 became =SUM(Qtr 4). Note that if you copy natural-language formulas to places that do not make sense, Excel alerts you that something is wrong. For example, if you copy the formula in cell H9 to cell J9, Excel displays the error value NAME? because no label is available in column J. If you change the column and row labels in tables, the labels you typed into formulas adjust. For example, if we change the label Total in Cell G3 of Figure 12-11 to Year, the formula in cell G9 adjusts accordingly to read =SUM(Year).

Naming Cells and Cell Ranges

If you find yourself repeatedly typing cryptic cell addresses, such as Sheet3!A1:AJ51, into formulas, dont worryExcel has a better way. Assign a short, memorable name to any popular cell or range, and then use that name instead of the cryptogram in formulas.

After you define names in a worksheet, those names are made available to any other worksheets in the workbook. A name defining a cell range in Sheet6, for example, is available for use in formulas in Sheet1, Sheet2, and so on in the workbook. As a result, each workbook contains its own set of names. You can also define worksheet-level names that are available only on the worksheet in which they are defined.

Tip: Do not define names for simple tables In a simple table with row and column headings, you can use the headings themselves in formulas located in the same rows and columns, without first having to define names.

Using Names in Formulas

When you use the name of a cell or a range in a formula, the result is the same as if you entered the cell or range address. For example, suppose you entered the formula =A1+A2 in cell A3. If you defined the name Mark as cell A1 and the name Vicki as cell A2, the formula =Mark+Vicki has the same result. The easiest way to define a name follows:

1 Select a cell.
2 Click the Name box in the formula bar.
3 Type TestName, and then press Enter.
Use the Name box in the formula bar to quickly assign names to cells and ranges. Keep the following basics in mind when using names in formulas:

The Name box normally displays the address of the selected cell. If the selected cell or range is named, the name takes precedence over the address and is displayed in the name box

When you define a name for a range of cells, the range name does not appear in the Name box unless the entire range is selected. When you click the Name box and select a name, the cell selection switches to the named cells.

If you type a name that has already been defined into the Name box, Excel switches the selection instead of redefining the name.

When you define a name, the address includes the worksheet name and the cell reference is absolute. For example, when you define the name TestName for cell C5 in Sheet1, the actual name definition is recorded as Sheet1!$C$5. For more information about absolute references.

Defining and Managing Names:

Instead of coming up with new names for cells and ranges, you can simply use existing text labels to create names. Choosing Insert, Name, Define, you can use text in adjacent cells to define cell and range names, as seen in Figure 12-14. You can choose this command also to redefine existing names.

Rules for Naming:

The following rules apply when you name cells and ranges in Excel:

Tip: Press Ctrl+F3 to display the Define Name dialog box instantly.

If you select the range you want to name before choosing the Insert, Name, Define command, and you are happy using the adjacent label as a name, just press Enter to define the name. The next time you open the Define Name dialog box, the name appears in the Names In Workbook list, which displays all the defined names for the workbook. You can define a name also without first selecting a cell or range in the worksheet. For example, in the Define Name dialog box, type Test2 in the Names In Workbook box and then type =D20 in the Refers To box. Click Add to add the name to the list. The Define Name dialog box remains open, and the Refers To box displays the name definition =Sheet1!D20. Excel adds the worksheet reference for you, but note that the cell reference stays relative, just as you entered it. If you do not enter the equal sign preceding the reference, Excel interprets the definition as text. For example, if you typed D20 instead of =D20, the Refers To box would display the text constant ="D20" as the definition of the name Test2. When the Define Name dialog box is open, you can insert references in the Refers To box also by selecting cells in the worksheet. If you name several cells or ranges in the Define Name dialog box, be sure to click Add after entering each definition. (If you click OK, Excel closes the dialog box.) When you choose Insert, Name, Define, any label in an adjacent cell in the same row or column is suggested as a name.

Editing Names

To redefine an existing cell or range name in the Define Name dialog box, first select the name in the Names In Workbook list and then edit the cell or range reference in the Refers To box. You can either type a new reference or select a new cell or range directly in the worksheet while the Define Name dialog box is open. To delete a name in the Define Name dialog box, select the name from the Names In Workbook list and then click Delete. Keep in mind that when you delete a name, any formula in the worksheet that refers to that name returns the error value #NAME?

Naming Constants and Formulas

You can create names that are defined by constants and formulas instead of by cell references. You can use absolute and relative references, numbers, text, formulas, and functions as name definitions. For example, if you often use the value 8.3 percent to calculate sales tax, you can choose Insert, Name, Define, type the name Tax in the Names In Workbook box, and then type 8.3% (or .083) in the Refers To box. Then you can use the name Tax in a formula, such as =Price+(Price*Tax), to calculate the cost of items with 8.3 percent sales tax. Note that named constants and formulas do not appear in the Name box in the formula bar, but they do appear in the Define Name dialog box. You can also enter a formula in the Refers To box. For example, you might define the name Price with a formula, such as =Sheet1!A1*190%. If you define this named formula while cell B1 is selected, you can then type =Price in cell B1, and the defined formula takes care of the calculation for you. Because the reference in the named formula is relative, you could then type =Price into any cell in your workbook to calculate a price using the value in the cell directly to the left. If you enter a formula in the Refers To box that refers to a cell or range in a worksheet, Excel updates the formula whenever the value in the cell changes. Using Relative References in Named Formulas When you are creating a named formula that contains relative references, such as =Sheet1!B22+1.2%, Excel interprets the position of the cells referenced in the Refers To box as relative to the cell that is active when you define the name. Later, when you use such a name in a formula, the named formula uses whatever cell corresponds to the relative reference. For example, if cell B21 was the active cell when you defined the name Tax as =Sheet1!B22+1.2%, the name Tax always refers to the cell one row below the cell in which the formula is currently located.

Creating Three-Dimensional Names

You can create three-dimensional names, which use 3-D references as their definitions. For example, suppose you have a 13-sheet workbook containing one identical sheet for each month plus one summary sheet. You can define a 3-D name that can be used to summarize totals from each monthly sheet. To do so, follow these steps:

1 Select cell B5 in Sheet1 (the Summary sheet).
2 Choose Insert, Name, Define.
3 Type Three_D (or any name you choose) in the Names In Workbook box and type =Sheet2:Sheet13!B5 in the Refers To box.
4 Press Enter.

Now you can use the name Three_D in formulas that contain any of the following functions:
SUM, MIN, VAR, COUNTA, PRODUCT, VARP, AVERAGE, STDEV, COUNT, MAX, and STDEVP.

For example, the formula =MAX(Three_D) returns the largest value in the three dimensional range named Three_D. Because you used relative references in step 3, the definition of the range Three_D changes as you select different cells in the worksheet. For example, if you select cell C3 and display the Define Name dialog box, =Sheet2:Sheet13!C3 appears in the Refers To box.

Pasting Names into Formulas

After you define one or more names in your worksheet, you can insert those names in formulas using the Paste Name dialog box. Use the Paste Name dialog box to insert names in your formulas. For example, to paste the name Qtr_1 into a formula:
1 Type an equal sign and then type the operators, functions, or constants of your formula.
2 Place the insertion point in the formula where you want to insert the name, and then choose Insert, Name, Paste (or press F3).
3 Select Product_1, and click OK to insert the name in the formula.
4 Type any other operands and operators to complete the formula, and then press Enter.

Using the previous example, Paste Names may not seem like a very compelling feature, unless your defined names are too long or cryptic to reliably type into formulas.

Creating a List of Names

In large worksheet models, its easy to accumulate a long list of defined names. To keep a record of all the names used, you can paste a list of defined names in your worksheet by clicking Paste List in the Paste Name dialog box. Excel pastes the list in your worksheet beginning at the active cell.
Replacing References with Names To replace references in formulas with their corresponding names, choose Insert, Name, Apply. Excel locates all cell and range references for which you have defined names and changes them. If you select a single cell before you choose the Apply command, Excel applies names throughout the active worksheet; if you select a range of cells, Excel applies names to only the selected cells. Figure 12-18 shows the Apply Names dialog box, which lists all the cell and range names you have defined. If you do not want to include all the names, click a name in the list to clear it; click it again to reselect it. Understanding Error Values An error value is the result of a formula that Excel cant resolve. The seven error values are described in Table 12-2. Table 12-2. Error Values 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 entered a name in a formula that isnt in the Define Name dialog box list. 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 that 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. Table 12-2. Error Values Worksheet Calculation Excel developers used to wear T-shirts emblazoned with the battle cry Recalc or Die. Rest assured that no developer has ever been harmed during the creation of any version of Excel. The deceptively simple process of calculation computes all formulas and then displays the results in the cells that contain them. When you change the values in the cells to which these formulas refer, Excel updates the values of the formulas as well. This updating process is called recalculation, and it affects only those cells containing references to cells that have changed. By default, Excel recalculates whenever you make changes to a cell. If a large number of cells must be recalculated, the words Calculating Cells and a number appear at the left end of the status bar. The number indicates the percentage of recalculation that has been completed. You can interrupt the recalculation process simply by using commands or making cell entries; Excel pauses and then resumes recalculation when you are finished. Recalculating Manually To save time, particularly when you are making entries into a large workbook with many formulas, you can switch from automatic to manual recalculation; that is, Excel will recalculate only when you tell it to. To set manual recalculation, choose Tools, Options and then click the Calculation tab to display the options shown in Figure 12-20. Here are a few things to remember about calculation options: To turn off automatic recalculation, select the Manual option. If you make a change that normally initiates recalculation, the status bar displays Calculate instead of recalculating automatically. The Recalculate Before Save option helps make sure that the most current values are stored on disk. To turn off automatic recalculation only for data tables, select the Automatic Except Tables option. For more information, see gUsing Data Tablesh on page 493. To recalculate all open workbooks, click the Calc Now button or press F9. To calculate only the active worksheet in a workbook, click the Calc Sheet button or press Shift+F9. Copying Formulas and Pasting Only Their Resulting Values This is undoubtedly one of the most often used features in Excel. Normally when you copy a cell that contains a formula, the formula is pasted as well, which is handy. But if you want to copy only the result, without the formula, choose Edit, Paste Special. The Paste Special dialog box appears, as shown in Figure 12-21. Among many other things, you can choose Paste Special to extract the results of formulas. To transfer only the resulting values of formulas, select the Values option. For more information about Paste Special options, see Pasting Selectively Using Paste Special on page 151. Evaluating Part of a Formula You might want to see the result of just one part of a complex formula if, for example, you are tracking down a discrepancy. To change only part of a formula to a value, select the part you want to change and press F9. You also can use this technique to change highlighted cell references in formulas to their values. Figure 12-22 shows an example. If youre just checking your figures, press the Esc key to discard the edited formula. Otherwise, if you press Enter, you replace the selected portion of the formula. Tip Use Evaluate Formula to troubleshoot You can also choose Tools, Formula Auditing, Evaluate Formula to troubleshoot your workbook models. For more information, see Evaluating and Auditing Formulas on page 381. Using Circular References A circular reference is a formula that depends on its own value. The most obvious type is a formula that contains a reference to the same cell in which its entered. For example, if you type =C1-A1 in cell A1, Excel displays the error message shown in Figure 12-23. f12ie23 Figure 12-23. This error message appears when you attempt to enter a formula that contains a circular reference. If a circular reference warning surprises you, this usually means that you made an error in a formula. Click OK, and look at the formula. If the error isnt obvious, check the cells that the formula refers to. Choose View, Toolbars, Circular Reference to help track down the problem using the built-in auditing features of Excel. Click the Trace Precedents or Trace Dependents button to draw tracer arrows on the worksheet. These arrows show you a visual path to all the cells involved, as shown in Figure 12-24. f12ie24 Figure 12-24. Display the Circular Reference toolbar to track down unwanted circular references. If you click the Trace Precedents button, Excel draws a line from the cell that contains the formula to any cells used by the formula. Each time you click, tracer lines appear for the next level, if any of the cells used by the formula contain formulas themselves. Note For additional information about auditing tools, see Auditing and Documenting Worksheets on page 251. Many circular references can be resolved. Some circular formulas are useful or even essential, such as the set of circular references in Figure 12-25. These formulas are circular because the formula in cell M29 depends on the value in M30, and the formula in M30 depends on the value in M29. Note You can find the sample file used in this example, Sales Invoice.xls, on the companion CD. After you dismiss the error message shown in Figure 12-23, the formula will not resolve until you allow Excel to recalculate in controlled steps. To do so, choose Tools, Options, click the Calculation tab, shown in Figure 12-20 on page 380, and then select the Iteration option. Excel recalculates all the cells in any open worksheets that contain a circular reference. If necessary, the recalculation repeats the number of times specified in the Maximum Iterations box (100 is the default). Each time Excel recalculates the formulas, the results in the cells get closer to the correct values. If necessary, Excel continues until the difference between iterations is less than the number entered in the Maximum Change box (0.001 is the default). Thus, using the default settings, Excel recalculates either a maximum of 100 times or until the values change less than 0.001 between iterations, whichever comes first. If the word Calculate appears in the status bar after the iterations are finished, more iterations are possible. You can accept the current result, increase the number of iterations, or lower the Maximum Change threshold. Excel does not repeat the Cannot resolve circular reference error message if it fails to resolve the reference. You must determine when the answer is close enough. Excel can perform iterations in seconds, but in complex circular situations, you might want to set the Calculation option to Manual; otherwise, Excel recalculates the circular references every time you make a cell entry. The useful circular reference scenario described in this section is called convergence: The difference between results becomes smaller with each iterative calculation. In the opposite process, called divergence, the difference between results becomes larger with each calculation. Excel continues iterations until it completes the number you specify. Understanding the Precision of Numeric Values Here are three interesting facts about numeric precision in Excel: Excel stores numbers with as much as 15-digit accuracy and converts any digits after the fifteenth to zeros. Excel drops any digits after the fifteenth in a decimal fraction. Excel uses scientific notation to display numbers that are too long for their cells. Table 12-3 contains examples of how Excel treats integers and decimal fractions longer than 15 digits when they are entered in cells with the default column width of 8.43 characters: Excel can calculate positive values as large as 9.99E+307 and approximately as small as 1.00E..307. If a formula results in a value outside this range, Excel stores the number as text and assigns a #NUM! error value to the formula cell. Troubleshooting Rounded values in my worksheet donft add up Your worksheet can appear erroneous if you use rounded values. For example, if you use cell formatting to display numbers in currency format with two decimal places, the value 10.006 is displayed as the rounded value $10.01. If you add 10.006 and 10.006, the correct result is 20.012. If all of these numbers are formatted as currency, however, the worksheet displays the rounded values $10.01 and $10.01, and the rounded value of the result is $20.01. The result is correct, as far as rounding goes, but its appearance might be unacceptable for a particular purpose, such as a presentation or an audit. You can correct this problem by changing the currency format, or you can choose Tools, Options and then select the Precision As Displayed option on the Calculation tab. However, you should select this option only with extreme caution because it permanently changes the underlying values in your worksheet to their displayed values. For example, if a cell containing the value 10.006 is formatted as currency, selecting the Precision As Displayed option permanently changes the value to 10.01. For more information about number formatting, see gFormatting in Cellsh on page 200. Table 12-3. Examples of Numeric Precision Typed Entry Displayed Value Stored Value 123456789012345678 1.23457E+17 123456789012345000 1.23456789012345678 1.234568 1.23456789012345 1234567890.12345678 1234567890 1234567890.12345 123456789012345.678 1.23457E+14 123456789012345 Using Arrays Arrays are familiar concepts to computer programmers. Simply defined, an array is a collection of items. Excel is one of the few applications that facilitate array operations, in which items that comprise an array can be individually or collectively addressed in simple mathematical terms. Here is some basic array terminology you should know: An array formula acts on two or more sets of values, called array arguments, to return either a single result or multiple results. An array range is a block of cells that share a common array formula. An array constant is a specially organized list of constant values that you can use as arguments in your array formulas. Arrays perform calculations in a way unlike anything else. They can be used for worksheet security, alarm monitors, linear regression tables, and much more. One-Dimensional Arrays The easiest way to learn about arrays is to look at a few examples. For instance, you can calculate the averages shown in Figure 12-26 by entering a single array formula. f12ie26 Figure 12-26. We entered a single array formula in the selected range F4:F8. This particular example might be used to help protect the formulas from tampering because modifying individual formulas in cells that are part of an array is impossible. To enter this formula, do the following: 1 Select the range F4:F8. 2 Type the formula into the formula bar, as shown in Figure 12-26. 3 Press Ctrl+Shift+Enter. The resulting single array formula exists in five cells at once. Although the array formula seems to be five separate formulas, you canft make changes to any one formula without selecting the entire formula.that is, the entire range F4:F8. Array Formula Rules To enter an array formula, first select the cell or range that will contain the results. If the formula produces multiple results, you must select a range the same size and shape as the range or ranges on which you perform your calculations. Follow these guidelines when entering and working with array formulas: Press Ctrl+Shift+Enter to lock in an array formula. Excel will then place a set of curly braces around the formula in the formula bar to indicate that itfs an array formula. Donft type the braces yourself; if you do, Excel interprets your entry as a label. You canft edit, clear, or move individual cells in an array range, nor can you insert or delete cells. You must treat the cells in the array range as a single unit and edit them all at once. To edit an array, select the entire array, click the formula bar, and edit the formula. Then press Ctrl+Shift+Enter to lock in the formula. To clear an array, select the entire array and press Delete. To select an entire array, click any cell in the array and press Ctrl+/. To move an array range, you must select the entire array and either cut and paste the selection or drag the selection to a new location. You canft cut, clear, or edit part of an array, but you can assign different formats to individual cells in the array. You can also copy cells from an array range and paste them in another area of your worksheet. Two-Dimensional Arrays In the preceding example, the array formula resulted in a vertical, one-dimensional array. You also can create arrays that include two or more columns and rows, otherwise known as two-dimensional arrays. An example is shown in Figure 12-27. f12ie27 Figure 12-27. We used a two-dimensional array formula in B10:E14 to compute the rank of each exam score. A similar one-dimensional array is in F10:F14. To enter a two-dimensional array, do the following: 1 Select a range to contain your array that is the same size and shape as the range you want to work with. 2 Type your formula into the formula bar, and press Ctrl+Shift+Enter. Note Unfortunately, you cant create three-dimensional arrays across multiple worksheets in workbooks. Single-Cell Array Formulas You can perform calculations on a vast collection of values within a single cell, using an array formula that produces a single value as a result. For example, to count the number of error values in a range of cells, you can create a single-cell array formula, as shown in Figure 12-28. f12ie28 Figure 12-28. We used a single-cell array formula in A3 to count error values that appear in the range B4:N38. In the example shown in Figure 12-28, the formula can be entered as a normal formula (press Enter) or as an array formula (press Ctrl+Shift+Enter). In this case, using an array formula makes a difference. No matter how many errors might appear in the worksheet, the nonarray version of the formula returns the value 1 because the SUM function essentially has only one argumentthe result of the single ISERROR function. If you enter the same formula as an array formula, however, it returns the total number of errors because the SUM function sees an array of individual ISERROR functions instead of just one. For more on the ISERROR function, see Using the IS Information Functions on page 425. Using Array Constants An array constant is a specially organized list of values that you can use as arguments in your array formulas. Array constants can consist of numbers, text, or logical values. You must enclose an array constant in braces{ and }and separate its elements with commas and semicolons. Commas indicate values in separate columns, and semicolons indicate values in separate rows. The formula in Figure 12-29, for example, performs nine computations in one cell. f12ie29 Figure 12-29. An array constant is the argument for this array formula. To enter a formula using an array constant, follow these steps: 1 Select a range of cells the size you need to contain the result. In Figure 12-29, the argument to the INT function contains three groups (separated by semicolons) of three values (separated by commas), which produces a three-row, three-column range. 2 Type the braces around the array argument, to indicate that the enclosed values make up an array constant. (Note that array arguments differ from array formulas, in which you cannot type the braces.) 3 Press Ctrl+Shift+Enter. The resulting array formula contains two sets of curly braces one set encloses the array constant and the other encloses the entire array formula. When entering array constants, remember that commas between array elements place those elements in separate columns, and semicolons between array elements place those elements in separate rows. Understanding Array Expansion When you use arrays as arguments in a formula, all your arrays should have the same dimensions. If the dimensions of your array arguments or array ranges do not match, Excel often expands the arguments. For example, to multiply all the values in cells A1:B5 by 10, you can use either of the following array formulas: { =A1:B5*10} or { ={ 1,2;3,4;5,6;7,8;9,10} *10}. Note that these two formulas are not balanced; 10 values are on the left side of the multiplication operator but only one is on the right. Excel expands the second argument to match the size and shape of the first. In the preceding example, the first formula is equivalent to { =A1:B5*{ 10,10;10,10;10,10;10,10;10,10} } and the second is equivalent to { ={ 1,2;3,4;5,6;7,8;9,10} *{ 10,10;10,10;10,10;10,10;10,10} }. When you work with two or more sets of multivalue arrays, each set must have the same number of rows as the argument with the greatest number of rows and the same number of columns as the argument with the greatest number of columns. Creating Conditional Tests A conditional test formula compares two numbers, functions, formulas, labels, or logical values. Conditional tests can be used to flag values that fall below or above a given threshold, for example. You can use simple mathematical and logical operators to construct logical formulas, or you can use an assortment of built-in functions. For information about using conditional test functions, see Understanding Logical Functions on page 421. Each of the following formulas performs a rudimentary conditional test: =A1>A2 =5-3<5*2 =AVERAGE(B1:B6)=SUM(6,7,8) =C2=Female =COUNT(A1:A10)=COUNT(B1:B10) =LEN(A1)=10 Every conditional test must include at least one logical operator, which defines the relationship between elements of the conditional test. For example, in the conditional test A1>A2, the greater than (>) logical operator compares the values in cells A1 and A2. Table 12-4 lists the six logical operators. Table 12-4. Logical Operators Operator Definition = Equal to > Greater than < Less than > = Greater than or equal to < = Less than or equal to < > Not equal to The result of a conditional test is either the logical value TRUE (1) or the logical value FALSE (0). For example, the conditional test =A1=10 returns TRUE if the value in A1 equals 10 or FALSE if A1 contains any other value. Using the Conditional Sum and Lookup Wizards Excel includes two useful tools called wizards that help you assemble frequently used yet confusing types of formulas. The Conditional Sum Wizard and the Lookup Wizard are provided as add-ins, which are special types of macros designed to integrate seamlessly into Excel. To see whether you have these Wizards installed, look at the Tools menu. If you see a Conditional Sum or Lookup commands, then the respective Wizards are installed. If you dont see either command, choose Tools, Addins. If Conditional Sum Wizard and Lookup Wizard are on the list of available add-ins, select both (and any others you want) and then click OK to install them. If neither add-in is on the list, you need to run Setup to install them. For more information about Setup, see Appendix A, Installing Microsoft Excel. Creating Conditional Sum Formulas The Conditional Sum Wizard creates formulas using the SUM and IF functions. This Wizard not only makes the construction of these formulas easier and faster, but also shows you how these formulas are constructed so that you can build your own conditional formulas without the Wizard. For more information about the IF function, see Understanding Logical Functions on page 421X. To build a conditional formula: 1 Select the table or list containing the values you want to use, and choose Tools, Conditional Sum to display the dialog box shown in Figure 12-31. If you click anywhere in the table before you start the Wizard, Excel automatically selects the current region for you. If Excel selects the correct region, click Next. Otherwise, drag to select the range you want to use. Remember to include the row and column labels. After clicking Next, the dialog box changes to the one shown on the left in Figure 12-32. 2 In the Column To Sum list, select the name of the column from which you want to extract totals. This is why you need to select the labels in the Step 1 dialog box. If the column labels do not appear in the list, click Back and reselect the range. 3 Still in Step 2 of the Wizard, specify the condition to use in selecting the values you want to include in the total. In the Column list, select the name of the column containing the labels you want to conditionally check, select an operator in the Is list, and then select a value in the This Value list. The contents of the This Value list change depending on the column selected in the Column list. The This Value list displays only the unique values in the selected column, ignoring duplicates. 4 Click Add Condition. The criteria you specify are added to the list at the bottom of the dialog box. You can add as many as seven conditions. If you change your mind about any condition, select the condition from the list and click Remove Condition. When you have finished removing conditions, click Next. 5 In Step 3 of the Wizard, choose either Copy Just The Formula To A Single Cell, or choose Copy The Formula And Conditional Values. 6 Click Next, and then select the cell where you want to place the resulting formula. If you chose the Copy Formula And Conditional Values option in Step 3 of the Wizard, the Wizard adds an extra step. Select the cell where you want the conditional value to go. Then click Next, and click the cell where you want the formula to go. 7 Click Finish. The resulting formula (and the optional conditional value) is pasted in the worksheet in the locations specified. Inside Out Beware of the Wizard In many cases, including Step 1 of the Conditional Sum Wizard (shown in Figure 12-31), Excel attempts to select a cell range or table for you automatically. This doesnt always work the way it should. In the example worksheet shown in Figure 12-31, Excel automatically selected A1:C20, which includes a merged cell at the top; this isnt allowed. We had to select the correct range before proceeding. Excel should recognize this before automatically selecting it, but it doesnt. You can add more conditional formulas, or if you already have a list of unique values (such as salesperson names), you can copy the formula as needed (but only if you used the Copy Formula And Conditional Values option in Step 3 of the Wizard), as shown in Figure 12-33. Creating Lookup Formulas The Lookup Wizard creates formulas using the INDEX and MATCH functions. Like the Conditional Sum Wizard, it makes the construction of lookup formulas easier and faster, and it also illustrates how these formulas are constructed so that you can build them yourself later. For more information about the INDEX and MATCH functions, see Understanding Lookup and Reference Functions on page 426. To build a lookup formula: 1 Choose Tools, Lookup to display the dialog box shown on the left in Figure 12-34. 2 Select the table or list containing the values you want to use. If you click anywhere in the table before you start the Wizard, Excel automatically selects the current region for you. If Excel selects the correct region, click Next; otherwise drag to select the range you want to use. Remember to include the row and column labels. The dialog box changes to the one shown on the right in Figure 12-34. 3 Select the name of the column containing the value you want from the Select The Column Label list. (This is why you need to select the labels in the Step 1 dialog box.) If the labels dont appear in the list, click the Back button and reselect the range. 4 Decide whether you want the lookup parameters as well as the result to be inserted in your worksheet, as shown in Figure 12-35. Inserting the parameters (conditions) is recommended, as we will show later. Select the Copy The Formula And Lookup Parameters option, and then click Next. f12ie35 Figure 12-35. If you copy the formula to a single cell, the parameters are fixed; copying both the formula and the parameters allows you to create a lookup table. 5 Select the cell where you want the resulting formula to be placed. If you chose the Copy The Formula And Lookup Parameters option in Step 3 of the Wizard, the Wizard adds two extra steps. If you did this, select the cell where you want the first parameter to go, click Next, and click a cell from the second parameter. Then click Next and click the cell where you want the conditional formula to go. 6 Click Finish. As mentioned previously, when you select the Copy The Formula And Lookup Parameters option in Step 3 of the Lookup Wizard, the parameters are inserted in your worksheet; in our example, we specified cells P5 and Q5. The resulting lookup formula (in cell R5) refers to these inserted values using relative references. As you can see in the formula bar in Figure 12-36, the first arguments for the MATCH functions are relative references to our specified cells. Used in this way, you can do two things. You can type other valid parameters (Sept, or Product 12, or both, for example) into the parameter cells (P5 and Q5), and the lookup formula finds the corresponding value at the new intersection. Two, because the parameter references are relative, you can copy the formula to additional cells and type additional parameters into cells in the same relative locations.