even when the value or formula
`• You can give a name to a value or formula -
`doesn't exist on the worksheet. For example, you can create
`the name
`Jnterest_Rate
`for a value of .075. Then, you can use this name in your formulas
`(more about
`this in the next chapter).
`
`Valid Names
`Although Excel is quite flexible about
`some rules:
`
`the names
`
`that you can define,
`
`it does have
`
`or a
`• Names can't contain any spaces. You might want to use an underscore
`period character
`to simulate a space (such as Annual_Total or Annual.Total).
`
`of letters and numbers, but the name must
`• You can use any combination
`begin with a letter. A name can't begin with a number
`(such as 3rdQuarter)
`look like a cell reference
`(such as Q3).
`
`or
`
`it's not
`and periods, aren't allowed. Although
`• Symbols, except for underscores
`documented.
`I've found that Excel also allows a backslash
`(\) and question
`mark(?).
`
`a name anywhere near
`to 255 characters. Trust me-using
`• Names are limited
`is not a good idea; in fact, it defeats
`this length
`the purpose of naming ranges.
`• You can use single letters
`( except for R or C), but this is generally not recom-
`mended because,
`again, it defeats
`the purpose of using rneanini:,.<ful names.
`
`for its own use. Although you can create
`intenially
`Excel also uses a few names
`names
`that override Excel's internal names, you should avoid doing so. To be on
`the safe side, avoid using the following for names: Print_Area, Print_ Titles,
`Consoliclate_Area,
`and Sheet_ Title.
`
`You can use labels that appear as row and column headings as names; when you
`do, you don't actually have to define the names. This feature is most useful when
`you use formulas, so it is discussed in detail in Chapter 9.
`
`Creating Names Manually
`Several ways exist to create names. This sedion discusses
`names manually.
`
`two methods
`
`to create
`
`Using the Define Name dialog box
`the cell or range that you want to name.
`To create a range name, start by selecting
`Then, select Insert• Name• Define (or press Ctrl+F3). Excel displays
`the Define
`Name dialog box, shown
`in Figure 8-16.
`
`emnosUSA0016820
`
`CiM Ex. 1052 Page 1
`
`

`
`Figure 8-16: Create names for cells or ranges by
`using the Define Name dialog box.
`
`(or use the name that Excel
`in workbook
`Type a name in the box labeled Names
`if any). The active or selected
`proposes,
`cell or range address
`appears
`in the Refers
`to box. Verify that the address
`listed
`is correct and then click OK to add the name
`to your worksheet
`and dose
`the dialog box. Or, you can click the Add button
`to
`If you do this, you must specify
`continue
`adding names
`to your worksheet.
`the
`Refers to range either by typing an address
`(make sure to begin with an equal sign)
`or by pointing
`to it in the worksheet. Each name appears
`in the list box.
`
`Using the Name box
`A faster way to create a name is to use the Name box. Select the cell or range to
`name and then click the Name box and type the name. Press Enter to create
`the
`If a name already exists, you can't use the Name box to change
`name.
`the range to
`which
`that name refers. Attempting
`to do so simply selects
`the range.
`
`When you enter a name in the Name box, you must press Enter to actually record
`the name. If you type a name and then click in the worksheet, Excel won't create
`the name.
`
`(see Figurn
`in the workbook
`The Name box is a drop--d.nwn list and shows all names
`the name.
`8-17). To choose a named cell or range, click the Name box and choose
`The name appears
`in the Name box, and Excel selects
`the named cell or range in
`the worksheet. Oddly enough, you can't open the Name box by using the keyboard;
`you must use a mouse. After you click the Name box, however, you can use the
`direction keys and Enter to choose a name.
`
`emnosUSA0016821
`
`CiM Ex. 1052 Page 2
`
`

`
`Figure 8-17: The Name box shows all names defined in the workbook.
`
`Creating Names Automatically
`You may have a worksheet
`that contains
`text that you want to use for names for
`adjacent cells or ranges. Figure 8-18 shows an example of such a worksheet.
`In this
`case, you might want to use the text in column A to create names for the
`corresponding
`values
`in column B. Excel makes this very easy to do.
`
`Figure 8-18: Excel makes it easy to create
`names by using text in adjacent cells.
`
`emnosUSA0016822
`
`CiM Ex. 1052 Page 3
`
`

`
`the name text and the
`text, start by selecting
`To create names by using adjacent
`cells that you want to name (these can be individual cells or ranges of cells). The
`names must be adjacent
`to the cells that you're naming (a multiple selection
`is
`Insert• Name• Create ( or Ctrl+Shift+ F~~). Excel displays
`allowed). Then, choose
`the Create Names dialog box, shown
`in Figure 8-19. The check marks in this dialog
`box are based on Excel's analysis of the selected
`range. For example,
`if Excel finds
`that you create names based on the
`text in the first row of the selection,
`it proposes
`top row. If Excel didn't guess correctly, you can change
`the check boxes. Click OK
`and Excel creates
`the names.
`
`Figure 8-19: The Create Names dialog box.
`
`If the text contained in a cell would result in an invalid name, Excel modifies the
`name to make it valid. For example, if a cell contains the text Net Income (which is
`invalid for a name because it contains a space), Excel converts the space to an
`underscore character. If Excel encounters a value or a formula where text should
`be, however, it doesn't convert it to a valid name. It simply doesn't create a name.
`
`You should double-check the names that Excel creates. Sometimes, the Insert•
`Name• Create command works counterintuitively.
`
`emnosUSA0016823
`
`CiM Ex. 1052 Page 4
`
`

`
`the entire table,
`If you select
`Figure 8-20 shows a small table of text and values.
`choose
`lnsertName
`• Create, and accept Kxcel's suggestions
`(Top Row and Left
`Column options), you'll find that the name Produds
`doesn't
`refer to A2:A5, as you
`refers to B2:C5. If the upper-left cell of the selection
`would expect, but instead
`contains
`text and you choose
`the Top Row and Left Column options, Excel uses that
`text for the name of the entire data-excluding
`the top row and left column. So,
`that E..'{cel creates,
`take a minute
`to make sure that
`before you accept
`the names
`ranges.
`they refer to the correct
`
`Figure 8-20: Creating names from the data in this
`table may produce unexpected results.
`
`Creating a Table of Names
`After you create a Jarge number of names, you may need to know the ranges that
`each name defines, particularly
`if you're
`trying to track clown errors or document
`your work. Excel lets you create a list of all names
`in the workbook and their
`cones ponding addresses. To create a table of names, first move the cell pointer
`an empty area of your worksheet-the
`table is created at the active cell position
`and will overwrite
`any information
`at that location. Use the Insert• Name• Paste
`(or F3). Excel displays
`in Figure 8-21,
`the Paste Name dialog box, shown
`command
`which lists all the defined names. To paste a list of names, dick the Paste List
`button.
`
`to
`
`emnosUSA0016824
`
`CiM Ex. 1052 Page 5
`
`

`
`Figure 8-21: The Paste Name dialog box.
`
`Deleting Names
`it. Deleting a range name does
`If you no longer need a defined name, you can delete
`not delete
`information
`in the range; it can, however, make formulas
`in your
`workbook
`invalid.
`
`the Define Name dialog box. Choose the
`Choose Insert• Name• Define to display
`name that you want to delete from the list and then click the Delete button.
`
`Be extra careful when deleting names. If the name is used in a formula, deleting
`the name causes the formula
`to become
`invalid (it will display #NAME?).
`However, deleting a name can be undone, so if you find that formulas return
`#NAME? after you delete a name, select Edit• Undo to get the name back.
`
`the names
`that contain named cells or ranges,
`If you delete the rows or columns
`if cell Al on Sheetl
`contain an invalid reference. For example,
`is named Interest and
`you delete row 1 or column A, Interest
`then refers to =Sheetl!#REF! (that
`is, an
`ff you use Interest
`in a formula,
`the formula displays #REF.
`erroneous
`reference).
`
`emnosUSA0016825
`
`CiM Ex. 1052 Page 6
`
`

`
`Redefining Names
`the cell or range to which it
`After you define a name, you may want to change
`refers. Select Insert• Name• Define to display
`the Define Name dialog box. Click
`the name that you want to change and then edit the cell or range address
`in the
`Refers to edit box. If you want to, you can click the edit box and select a new cell or
`range by pointing
`in the worksheet.
`
`Excel automatically adjusts the cells to which your names refer. For example,
`is named Summary. If you delete a row above row 10,
`assume that cell AlO
`Summary then refers to cell A9. This is just what you would expect to happen, so
`you don 1t need to be concerned about it
`
`Changing Names
`If you
`the name.
`Excel doesn't have a simple way to change a name once you create
`create a name and then realize
`that it's not the name that you want-or,
`perhaps,
`that you spelled
`it incorrectly-you
`must create
`the new name and then delete
`the
`old name.
`
`Learning More About Names
`t,'H.:el offers some additional
`features with respect
`unmatched
`in any of its competitors.
`
`to using names -features
`
`These advanced naming features are most useful when working with formulas
`and therefore are discussed in Chapter 9.
`
`Summary
`
`involve cells and
`that
`operations
`the basic worksheet
`This chapter discusses
`copying, moving, deleting, and working
`ranges. These operations
`include selecting,
`with ranges
`that extend across multiple worksheets
`in a workbook. This chapter
`also introduces
`the topic of names, an important
`concept
`that can make your
`worksheets more readable and easier
`to maintain.
`
`emnosUSA0016826
`
`CiM Ex. 1052 Page 7
`
`

`
`Creating and
`Using Formulas
`
`Formulas are what make a spreadsheet
`
`so useful. Without
`formulas, a spreadsheet would be little more than a word
`table feature. A worksheet
`processor with a very powerful
`is essentially dead. Using formulas acids life
`without formulas
`and lets you calculate
`results from the data stored
`in the work(cid:173)
`sheet. This chapter
`introduces
`formulas and helps you get up
`to speed with these
`important
`elements.
`
`Introducing Formulas
`To add a formula to a worksheet. you enter it into a cell. You
`can delete, move, and copy formulas
`just like any other item of
`data. Formulas use arithmetic operators
`to work with values,
`text, worksheet
`functions, and other formulas
`to calculate a
`value in the cell. Values and text can be located
`in other cells,
`which makes changing data easy and gives worksheets
`their
`dynamic nature. For example, Excel recalculates
`formulas
`if the
`value in a cell used by the formula changes.
`In essence, you
`can see multiple scenarios quickly by changing
`the data in a
`worksheet and letting formulas do the work.
`
`A formula entered
`following elements:
`
`into a cell can consist of any of the
`
`such as + (for addition) and .,, (for
`• Operators
`multiplication)
`• Cell references
`
`(inc1uding named cells and ranges)
`
`• Values or text
`• Worksheet
`functions
`
`(such as SUM or AVERAGE)
`
`A formula can consist of up to 1,024 charac:ter·s. After you
`enter a formula
`into a cell, the cell displays
`the result of the
`formula. The formula
`itself appears
`in the formula bar when
`you select the cell, however.
`
`emnosUSA0016827
`
`CiM Ex. 1052 Page 8
`
`

`
`Here are a few examples of formulas:
`
`= 15W .05
`
`.05. This formula uses only values
`Multiplies 150 times
`and isn't all that useful.
`
`=Al+A2
`
`Adds the values
`
`in cells Al and A2.
`
`=lncome-E,"X:penses
`
`Subtracts
`Income.
`
`the cell named Expenses
`
`from the cell named
`
`=SlJM(Al:AI2)
`
`Adds the values
`
`in the range Al:A12.
`
`=Al=Cl2
`
`cell Al with cell Cl 2. If they are identical,
`Compares
`formula
`returns TRUE; otherwise,
`it returns FALSE.
`
`the
`
`Notice that formulas always begin with an equal sign so that Excel can distinguish
`formulas from text.
`
`Operators Used in Formulas
`Excel lets you use a variety of operators
`in your formulas. Table 9-1 lists the opera(cid:173)
`tors that E,'icel recognizes.
`In addition
`to these, Excel has many built-in functions
`that enable you to perform more operations.
`
`These functions are discussed in detail in Chapter 10.
`
`Operator
`
`Name
`
`/
`
`&
`
`>=
`
`<>
`
`Division
`
`Concatenation
`
`Logical comparison (greater than or equal to)
`
`
`
`! ! iiiil!~1ij1liii~!~~~ ~1~~,~i~liMiH~li~11••
`
`Logical comparison (not equal to)
`
`emnosUSA0016828
`
`CiM Ex. 1052 Page 9
`
`

`
`as you need (for1nulas can be quite
`as many operators
`Youcan, of course,use
`complex), Figure 9-1 .shows a worksheetv1rith aformula
`in cell B5, The formulllis as
`follows:
`
`inB2 and
`the valUe in B3 from the ~lue
`the formula subtracts
`Jn this example,
`the result by the v}:i:lue in B4.Jf the worksheet
`had names deffaed
`then murtiplies
`forthese
`cells, the fonnula would he a lot more readable. Here'sthe
`same fonx1ula
`after nanling the cells;
`
`I
`
`the importance of naming range$? The
`lo understand
`No,v, are you beginning
`.of fortntilas that use various operators.
`following are sorne addilional examples
`
`to produce Pttrt~
`
`the two text strings
`
`Joins (concatenates)
`23A.
`of cell Alwith ceUA2.
`the.contents
`Concatenates
`Concatenation works with values as well as. text. IfcellAl
`contains 123 and cell A2 contains 456, this formula would
`return th€ value 1234.S.6.
`Raises 6 to the third power (216).
`the cube rootof2l6
`(6),
`Returns
`less than the value in
`Returns TRUE if the value in cellAUs
`it returns FALSE. Logical comparison
`ceUA2; Otherwise,
`operators also wcn"k with text.If Al c.ontafoed Bill and A2
`f onnula would return TRUE, because Bill
`contained Julia,the
`comes before Julia in alphabetical order.
`Returns TRUE if the value in cell Al is less than or equalto
`the valtJe in cell A2. Otherwise,
`it returns FALSE.
`
`emnosUSA0016829
`
`CiM Ex. 1052 Page 10
`
`

`
`=Al<>A2
`
`Returns TRUE if the value in cell Al isn't equal to the value
`in cell A2. Otherwise,
`it returns FALSE.
`
`Operator Precedence
`the order
`to control
`In an earlier example, parentheses
`are used in the formula,
`which the calculations
`occur. The formula. without parentheses
`looks like this:
`
`in
`
`=Income-Expenses*TaxRate
`
`the wrong
`Excel computes
`the parentheses,
`the formula without
`If you enter
`a concept called
`answer. To understand why this occurs, you need to understand
`operator precedence, which basically
`is the set of rules that Excel uses to perform
`its calculations. Table 9-2 lists Excel's operator
`p1·ecedence. This table shows
`that
`is, it's perfom1ed
`first), and
`exponentiation
`has the highest precedence
`(that
`logical comparisons
`have the lowest precedence.
`
`to
`to override Excel's built-in order of pt·ecedence. Returning
`You use parentheses
`the previous example,
`the formula
`that follows doesn't use parentheses
`and, there(cid:173)
`fore, is evaluated using Excel's standard operator precedence. Because multiplica(cid:173)
`tion has a higher precedence,
`the E.,'{pense cell is multiplied by the TaxRate cell.
`Then. this result is subtracted
`from Income. This isn't what was intended.
`
`the order of
`to control
`formula, which follows, uses parentheses
`The correct
`operations. Expressions within parentheses
`first. In this case,
`a.re always evaluated
`from Income and the result
`is subtracted
`is multiplied by TaxRate.
`Expenses
`
`=(Income-Expenses)*TaxRate
`
`Symbol Operator
`
`Precedence
`
`*
`
`+
`
`&
`
`<
`
`Multiplication
`
`Addition
`
`Concatenation
`
`Less than
`
`2
`
`3
`
`4
`
`5
`
`emnosUSA0016830
`
`CiM Ex. 1052 Page 11
`
`

`
`You can also nest parentheses
`inside
`in formulas, which means putting parentheses
`If you do so, Excel evaluates
`of parentheses.
`the most deeply nested expressions
`first and works
`its way out. Figure 9-2 shows an example of a formula
`that uses
`nested parentheses.
`
`Figure 9-2: A formula with nested parentheses.
`
`=((B2*C2)+(B3*C3)+(B4*C4))*B6
`
`inside the fourth
`sets are nested
`This formula has four sets of parentheses-three
`and then adds up the three
`set. Excel evaluates
`each nested set of parentheses
`results. This sum is then multiplied by the value in B6.
`
`is a good idea. I often use parentheses
`in your formulas
`liberally
`Using parentheses
`even when they aren't necessary,
`to clarify the order of operations
`and make the
`if you want to add 1 to the product of two cells,
`fonnula easier
`to read. For example,
`the following fonnula will do it:
`
`=l+AlkA2
`
`I find it much clearer, however,
`parentheses):
`
`=l+(Al*A2)
`
`to use the following formula
`
`(vlith superfluous
`
`If you
`right parenthesis.
`of course, must have a matching
`Every left parenthesis,
`have many levels of nested parentheses,
`it can sometimes
`be difficult to keep them
`If the parentheses
`straight.
`don't match, Excel displays a message explaining
`the
`problem and won't
`let you enter
`the formula. Fortunately, Excel lends a hand
`in
`helping you match parentheses. When you enter or edit a formula
`that has
`parentheses,
`pay attention
`to the text. When the insertion point moves over a
`parenthesis,
`Excel momentarily
`bolds
`it and its matching parenthesis.
`This lasts
`for less than a second,
`so be alert.
`
`emnosUSA0016831
`
`CiM Ex. 1052 Page 12
`
`

`
`Excel may pro(cid:173)
`parentheses,
`if your formula contains mismatched
`ln some cases,
`pose a correction
`to your formula. Figure 9-3 shows an example of the Formula
`AutoCorrect
`feature. You may be tempted
`simply to accept
`the proposed correc(cid:173)
`tion, but be careful-in
`many cases,
`the proposed
`formula, although syntactically
`correct,
`isn't the formula
`that you want.
`
`Figure 9-3: Excel's Formula AutoCorrect feature often
`suggests a correction to an erroneous formula.
`
`Excel's Built-in Functions
`that you can
`functions
`Excel provides a bewildering number of built-in worksheet
`use in your formulas. These include common functions
`(such as SUM, AVERAGE,
`and SQRT) as well as functions designed
`for special purposes,
`such as statistics or
`engineering. Functions can greatly enhance
`the power of your formulas. They can
`simplify your formulas and make them easier to read; in many cases, functions
`If you
`enable you to perform calculations
`that would not be possible otherwise.
`can't find a worksheet
`function
`that you need, Excel even lets you create your own
`custom
`functions.
`
`Excel's built-in functions are discussed in the next chapter, and Chapter 35 covers
`the basics of creating custom functions by using VBA.
`
`Entering Formulas
`
`earlier, a formula must begin with an equal sign to inform Excel that
`As mentioned
`the cell contains a formu]a rather
`than text. Basically, two ways exist to enter a
`formu1a into a cell: enter it manually or enter
`it by pointing
`to cell references. Each
`of these methods
`is discussed
`in the following sections.
`
`emnosUSA0016832
`
`CiM Ex. 1052 Page 13
`
`

`
`Entering Formulas Manually
`Entering a formula manually
`involves, well, entering a formula manually. You simply
`type an equal sign ( =), followed by the formula. As you type, the characters
`appear
`in the cell and in the formula bar. You can, of course, use all the normal editing keys
`when entering a formula.
`
`Entering Formulas by Pointing
`The other method of entering a formula still involves some manual
`typing, but you
`can simply point
`to the cell references
`instead of entering
`them manually. For exam(cid:173)
`ple, to enter the formula =Al+A2 into cell A3, follow these steps:
`
`1. Move the cell pointer
`2. Type an equal sign(=)
`in the status bar.
`
`to cell A3.
`
`to begin the formula. Notice that Excel displays Enter
`
`that E.'X:cel displays a
`this key, notice
`3. Press the up arrow twice. As you press
`faint moving border around
`the cell and that the cell reference appears
`in cell
`A3 and in the formula bar. Also notice that E..'X:cel displays Point in the status bar.
`4. Type a plus sign (+). The faint border disappears
`status bar.
`5. Press the up arrow one more time. A2 is added
`6. Press Enter to encl the formula.
`
`and Enter reappears
`
`in the
`
`to the formula.
`
`Pointing
`accurate
`
`to cell addresses
`and less tedious.
`
`rather
`
`than entering
`
`them manually
`
`is usually more
`
`When you create a formula that refers to other cells, the cell that contains the for(cid:173)
`mula has the same number format as the first cell it refers to.
`
`that you can use when you enter
`feature
`the Formula Palette
`Excel includes
`the Formula Palette, dick
`edit formulas. To display
`the Edit Formula button
`Formula bar (the Edit Formula button
`looks like an equal sign). The Formula
`Palette
`lets you enter formulas manually or use the pointing
`techniques
`described
`in Figure 9-4, displays
`the result of the
`previously. The Formula Palette, shown
`formula as it's being entered. The Formula Palette usually appears directly below
`the edit line, but you can drag it to any convenient
`location
`(as you can see in the
`figure).
`
`01·
`in the
`
`emnosUSA0016833
`
`CiM Ex. 1052 Page 14
`
`

`
`Figure 9-4: The Formula Palette displays the resultof the formula as it's being entered.
`
`If y()ur formula uses named ceUs or ranges, you can either type the name in place of
`the name from a list and haveExce:1 tnsert the narne for you
`the addr¢ss orchoose
`automatk:aJly. You have two ways available to inserta ~lame into a fonnttla:
`
`• Seleet ln$ert• Nrune• Pas.te: Ex:celdisplays
`cUalog boxwith
`its PasteName
`all the names listed { see Figure 9-5). Select the name artd click OK. Or, you can
`the name, whic:h inserts the n.ame and closes the dialog box.
`dottble-clkk
`the Paste Narne dialog box.
`• Press F3: This also displays
`
`Figure g .. sr The Paste Name
`diak1g box fetsyou
`insert a name
`into a formula.
`
`emnosUSA0016834
`
`CiM Ex. 1052 Page 15
`
`

`
`Referencing Cells Outside the Worksheet
`Formulas can refer to cells in other worksheets-and
`the worksheets don't even
`have to be in the same workbook. Excel uses a special type of notation
`to handle
`these types of references.
`
`Cells in Other Worksheets
`To use a reference
`to a cell in another worksheet
`following format:
`
`SheetName!CellAddress
`
`in the same workbook, use the
`
`the cell address with the worksheet name, followed by an
`Jn other words, precede
`exclamation point. Hel'e's an example of a formula that uses a cell on the Sheet2
`worksheet:
`
`=Al«Sheet2!Al
`
`This formula multiplies
`cell Al on Sheet2.
`
`the value in cell Al on the current worksheet by the value in
`
`If the worksheet name in the reference includes one or more spaces, you must
`enclose it in single quotation marks. For example, here's a formula that refers to a
`cell on a sheet named All Depts:
`=Al*'A-1 Depts'!Al
`
`Cells in Other Workbooks
`To refer to a cell in a different workbook, use this format:
`
`=[WorkbookName]SheetName!CellAddress
`
`the worksheet name, and an
`In this case, the workbook name (in square brackets),
`exclamation point precede
`the cell address. The following is an example of a
`formu1a that uses a cell reference
`in the Sheetl worksheet
`in a workbook named
`Budget:
`
`=rnuclget.xl
`
`s]Stieetl
`
`!Al
`
`includes one or more spaces, you must
`Jf the workbook name in the reference
`enclose
`it (and the sheet name) in single quotation marks. For example, here's a
`formu1a that refers to a cell on Sheetl
`in a workbook named Budget For 1999:
`
`=Al""' [Budget
`
`For 1999]Sheetl
`
`· !Al
`
`emnosUSA0016835
`
`CiM Ex. 1052 Page 16
`
`

`
`the other workbook doesn't
`When a formula refers to cells in a different workbook,
`need to be open. If the workbook
`is closed, you must add the complete path to the
`reference. Here's an example:
`
`=Al*'C:\ MSOffice\Excel\[Budget
`
`For 1999]Sneetl'
`
`!Al
`
`File linking is covered in detail in Chapter 19.
`
`Entering References to Cells Outside the Worksheet
`To create formulas
`that refer to cells not in the current worksheet, use the pointing
`earlier (refer to the section "Entering Formulas by Pointing").
`technique described
`Excel takes care of the details regarding
`the workbook and woi·ksheet
`references.
`The workbook
`that you're using in your formula must be open to use the pointing
`method.
`
`If you point to a different worksheet or workbook when creating a formula, you'll
`notice that Excel always inserts absolute cell references. Therefore, if you plan to
`copy the formula to other cells, make sure that you change the cell references to
`relative. This concept of absolute versus relative cell references is discussed in the
`following section.
`
`Absolute Versus Relative References
`
`You need to be able to distinguish between relative and absolate cell references. By
`in formulas except when the formula
`default, Excel creates Yelative cell references
`includes cells in different worksheets
`or workbooks. The distinction becomes
`apparent when you copy a formula to anothet· cell.
`
`Relative References
`Figure 9-5 shows a worksheet with a formula
`is as follows:
`default relative references.
`
`=82kC2
`
`in cell D2. The formula, which uses the
`
`When you copy this formula to the two cells below it, Excel doesn't produce an
`exact copy of the fo1·nmla; rather,
`it generates
`these fonnulas:
`
`• Cell D3: =B3*C3
`• Cell 04: =B4*C4
`
`emnosUSA0016836
`
`CiM Ex. 1052 Page 17
`
`

`
`to the new
`to refer to the cells that are relative
`the cell references
`Excel adjusts
`formu1a. Think of it like this: The original formula contained
`instn1ctions
`to multiply
`the value two cells to the left by the value one cell to the left. When you copy the
`instructions get copied, not the actual contents of the cell. Usually, this is
`cell, these
`if
`exactly what you want. You certainly don't want to copy the formula verbatim;
`you did, the new formulas would produce
`the same value as the original formula.
`
`Figure 9-6: The formula in cell D2 will
`be copied to the cell below.
`
`When you cut and paste a formula (move it to another location), the cell refer(cid:173)
`ences in the formula aren't adjusted. Again, this is what you usually want to hap(cid:173)
`pen. When you move a formula, you generally want it to continue to refer to the
`original cells.
`
`Absolute References
`to be copied verbatim. Figure 9-6
`Sometimes, however, you do want a cell reference
`that contains an absolute
`shows an example of a formula
`reference.
`In this example,
`cell B6 contains a sales tax rate. The formula
`in cell D2 is as follows:
`
`= C82"'C2) "'$B $6
`
`letter and
`the column
`to cell B6 has dollar signs preceding
`Notice that the reference
`the row number. These dollar signs indicate
`to E.,'i:cel that you want to use an
`absolute cell reference. When you copy this formula
`to the two cells below, Excel
`generates
`the following formulas:
`
`• Cell D3: =(B:3*C:3)*$B$6
`• Cell D4: =(84-"C4)*$8$6
`
`In this case, the relative cell references were changed, but the reference
`wasn't changed, because
`it's an absolute
`reference.
`
`to cell B6
`
`emnosUSA0016837
`
`CiM Ex. 1052 Page 18
`
`

`
`Figure 9-7: A formula that uses
`an absolute cell reference.
`
`Mixed References
`letter
`An absolute
`reference uses two dollar signs in its address: one for the column
`in which only one
`and one for the row number. Excel also allows mixed references
`is absolute. Table 9<l summarizes
`all the possible
`types of cell
`of the address parts
`references.
`
`When would you use a mixed reference? Figure 9-7 shows an example of a situation
`in which a mixed reference
`is appropriate. This worksheet will contain a table of
`values
`in which each cell consists of the value in column A multiplied by the value
`in row 1. The formula
`in cell B2 is as follows:
`
`=B$l*$A2
`
`Example Type
`
`$A$1
`
`Absolute reference
`
`A$1
`
`Mixed reference (row number is absolute)
`
`the row
`In the 8$1 reference,
`two mixed cell references.
`This formula contains
`number
`is absolute, but the column
`letter is relative. In the $A2 reference,
`the row
`number
`is relative, but the column
`letter is absolute. You can copy this formula
`to
`the range B2:ES and each cell will contain
`the correct
`formula. For example,
`the
`fornmla
`in cell ES would be as follows:
`
`=E$1*$A5
`
`emnosUSA0016838
`
`CiM Ex. 1052 Page 19
`
`

`
`Figure 9-8: This formula
`uses a mixed reference.
`
`Entering Nonrelative References
`You can enter nonrelative
`references
`(absolute or mixed) manually by inserting
`dollar signs in the appropriate
`positions. Or, you can use a handy shortcut:
`the F4
`key. When you're entering a cell reference-either
`manually or by pointing-you
`can press F4 repeatedly
`to have Excel cycle through all four reference
`types.
`
`if you enter =Al to start a formula, pressing F4 converts
`the cell
`For example,
`it to =A$1. Pressing
`it again displays
`to =$A$L Pressing F4 again converts
`reference
`=$Al. Pressing
`it one more time returns
`to the original =Al. Keep pressing F4 until
`Excel displays
`the type of reference
`that you want.
`
`When you name a cell or range, Excel (by default) uses an absolute reference for
`the name. For example, if you give the name SalesForecast to A 1 :A 12, the Refers
`to box in the Define Name dialog box lists the reference as $A$1 :$A$12. This is
`almost always what you want. If you copy a cell that has a named reference in its
`formula, the copied formula contains a reference to the original name.
`
`When a Formula Returns an Error
`
`Sometimes when you enter a formula, Excel displays a value that begins with a
`is returning an error value. You'll
`pound sign (#). This is a signal that the formula
`have to correct
`the formula (or correct a cell that the formula references)
`to get rid
`of the error display.
`
`As noted previously
`erroneous
`formula.
`
`in this chapter, Excel often suggests a correction
`
`for an
`
`If the entire cell is filled with pound characters, this means that the column isn't
`wide enough to display the value. You can either widen the column or change the
`number format of the cell.
`
`emnosUSA0016839
`
`CiM Ex. 1052 Page 20
`
`

`
`Table 9-4 lists the types of error values that may appear
`in a cell that has a formula.
`Formulas may return ,m error value if a cell to which they refer has an error value.
`single error value can make its way into lots
`This is known as the ripple effect-a
`of other cells that contain formulas
`that depend on the cell.
`
`Error Value Explanation
`
`#NAME?
`
`The formula uses a name that Excel doesn't recognize. This can happen if you
`delete a name that's used in the formula or if you have unmatched quotes
`when using text.
`
`#NULL!
`
`The formula uses an intersection of two ranges that don't intersect (this
`concept is described later in the chapter).
`
`#REF!
`
`The formula refers to a cell that isn't valid. This can happen if the cell has
`been deleted from the worksheet.
`
`Editing Formulas
`
`just like you can edit any other cell. You might need to
`You can edit your formulas
`if you make some changes
`to your worksheet
`and need to adjust the
`edit a formula
`fot·mLua to accommodate
`the changes. Or, the formula may return one of the enor
`values described
`in the previous section, and you need to edit the formula
`to
`the error.
`correct
`
`The following are the four ways to get into cell-edit mode:
`
`the cell, which enables you to edit the cell contents directly
`
`• Double-click
`the cell.
`• Press F2, which enables you to edit the cell contents directly
`
`in the cell.
`
`in
`
`emnosUSA0016840
`
`CiM Ex. 1052 Page 21
`
`

`
`• Select the cell that you want to edit, and then click in the formula bar. This
`enables you to edit the cell contents
`in the formula bar.
`
`• Click the Edit Formula button
`Palette.
`
`in the Formula bar to access
`
`the Formula
`
`Chapter 6 also discusses these methods.
`
`either by dragging
`While you're editing a formula, you can select multiple characters
`the mouse over them or by holding down Shift while you use the direction keys.
`
`Excel
`You might have a lengthy formula that you can't seem to edit cot·rectly-and
`won't let you enter it because of the error. In this case, you can convert
`the formula
`to text and tackle it again later. To convert a formula to text, just remove
`the initial
`equal sign ( = ). When you're ready to try again, insert
`the initial equal sign to
`convert
`the cell contents back to a formula.
`
`Changing When Formulas Are Calculated
`
`in your worksheet
`the formulas
`that Excel calculates
`You've probably noticed
`If you change any cells that the formula uses, Excel displays
`the
`immediately.
`formula's new result, with no effort on your par