a $125.00 . 4 $695.00 $2,780.00 3 $39.95 $119.85 Wb Chott “ehasko Zehesao aA m Figure 1 Copying a formula that contains relative references But what if the cell references in D2 contained absolute references, like this? =$BS2*$C$2 In this case, copying the formula to the cells below would produce incorrect results. The formula in cell D3 would be exactly the same as the formula in cell D2. Now I'll extend the example to calculate sales tax, which is stored in cell B7 (see Figure 2). In this situation, the formula in cell D2 is 2*C2*$BS7 The quantity is multi cell B7. id by the price, and the result is multiplied by the sales-tax rate stored in Notice that the reference to B7 is an absolute reference. When the formula in D2 is copied to the cells below it, cell D3 will contain this formula =B3*C3*$B$7A D rs F 1 item Sales Tax | Total 2 chair 4 $125.00 $37.50 2 [Desk 4 $695.00 4 lamp 3 $39.95 5 6 7 SalesTax: 7.50% 8 S 10 TaSTERAR] Chaat? hasta = igure 2 Formula references to the sales tax cell should be absolute. Here, the references to cells B2 and C2 were adjusted, but the reference to cell B7 was not— which is exactly what | want. Figure 3 demonstrates the use of mixed references. The formulas in the C3:F7 range calculate the area for various lengths and widths. The formula in cell C3 is ‘=$B3*C$2 reer: 1 2 1.0 3 1.0 1.0 «) & [o4s> as =| £ [vol 20 6 25 2.5 7 3.0 3.0 i 5 ; Figure 3 Using mixed cell references. Notice that both cell references are mixed. The reference to cell B3 uses an absolute reference for the column($B), and the reference to cell C2 uses an absolute reference for the row ($2). As a result, this formula can be copied down and across, and the calculations will be correct. For example, the formula in cell F7 is =$B7*F$2IF C3 used either absolute or relative references, copying the formula would produce incorrect results. Changing the types of your references You can enter non relative references (that is, absolute or mixed) manually by inserting dollar signs in the appropriate positions of the cell address. Or you can use a handy shortcut: the F4 key. When you've entered a cell reference (by typing it or by pointing), you can press Fa repeatedly to have Excel cycle through all four reference types. For example, if you enter =A1 to start a formula, pressing F4 converts the cell reference to =$AS1. PressingF4 again converts it to =A$1. Pressing it again displays =$A1. Pressing it one more time returns to the original=A1. Keep pressing Fé until Excel displays the type of reference that you want. Referencing cells outside the worksheet Formulas can also refer to cells in other worksheets—and the worksheets don’t even have to be in the same workbook. Excel uses a special type of notation to handle these types of references Referencing cells in other worksheets To use a reference to a cell in another worksheet in the same workbook, use this format: SheetName!CellAddress In other words, precede the cell address with the worksheet name, followed by an exclamation point. Here’s an example of a formula that uses a cell on the Sheet2 worksheet: =A1*Sheet2/A1 This formula multiplies the value in cell A1 on the current worksheet by the value in cell A1 on Sheet2. Referencing cells in other workbooks To refer to a cell in a different workbook, use this format: =[WorkbookName]SheetNamelCellAddress case, the workbook name (in square brackets), the worksheet name, and an exclamation point precede the cell address. The following is an example of a formula that uses a cell reference in the Sheet1worksheet in a workbook named Budget: =[Budget xlsxJSheet1!A1If the workbook name in the reference includes one or more spaces, you must enclose it (and the sheet name) in single quotation marks. For example, here’s a formula that refers to a cell on Sheet in a work book named Budget For 2008: 1 [Budget For [Link]]Sheet1'!A1 ‘When a formula refers to cells in a different workbook, the other workbook doesn’t have to be open. If the workbook is closed, however, you must add the complete path to the reference so that Excel can find it. Here's an example: =A1*'C:\My Documents\ [Budget For [Link]]Sheet1'!A1 A linked file can also reside on another system that’s accessible on your corporate network. The formula below, for example, refers to a cell in a workbook in the files directory of a computer named Data Server. \\DataServer\files\([Link]]Sheet1'1$D$7Using Formulas in Tables Summarizing data in a table Figure 4 shows a simple table with three columns. | entered the data, and then converted the range to atable by choosing Insert «> Tables «> Table. Note that | didn’t define any names, but the table is namedTable1 by default a 8 1 2 a ee ee] 3 Jan 4 Feb 5 Mer 5 apr 7 May 8 Jun 30 aug u Sep 2 ox a Now 14 bee WF i] Shest 2 Figure 48 simple table with three columns. If you'd like to calculate the total projected and total actual sales, you don’t even need to write a formula, ‘Simply click a button to add a row of summary formulas to the table: 1. Activate any cell in the table. 2. Place a check mark next to Table Tools *? Design «? Table Style Options *? Total Row. 3. Activate a cell in the Total Row and use the drop-down list to select the type of summary formula to use (see Figure 5). For example, to calculate the sum of the Actual column, select SUM from the drop down list in cell D15. Excel creates this formula: =SUBTOTAL(109,{Actual]) For the SUBTOTAL function, 109 is an enumerated argument that represents SUM. The second argument for the SUBTOTAL function is the column name, in square brackets. Using the column name within brackets Is a new way to create “structured” references within a table. (I discuss this further in an upcoming section, “Referencing data in a table.”)Figure 5 A drop-down lst enables you to select a summary formula fora table column. Using formulas within a table In many cases, you'll want to use formulas within a table. For example, in the table shown in Figure 5,you may want a column that shows the difference between the Actual and Projected amounts. 1. Activate cell E2 and type Difference for the column header. Excel automatically expands the table for you. 2. Next move to cell E3 and type an equal sign to signify the beginning of a formula. 3. Press the left arrow key. Excel displays [Actual], which is the column heading, in the Formula bar. 4, Type a minus sign and then press left arrow twice. Excel displays [Projected] in your formula. 5. Press Enter to end the formula. Excel copies the formula to all rows in the table. Figure 6 shows the table with the new column.2 Ce =) 4 Feb 000 au 6 pe 000 458 3 sal 000 5309 20 ug 6.000 se ut Sep, 000 sm 2 oot 3,000 os35 2 Nov 000 755 OM Sheth A et Figure 6The Difference column contains a formula. If you examine the table, you'll find this formula for all cells in the Difference column: =[Actual]-[Projected] Although the formula was entered into the first row of the table, that’s not necessary. Any time a formula Is entered into an empty table column, it will automatically fil all the cells in that column. And if you need to edit the formula, Excel will automatically copy the edited formula to the other cells in the column. ‘The steps listed above used the pointing technique to create the formula. Alternatively, you could have entered it manually using standard cell references. For example, you could have entered the following formula in cell E3: =D3-C3, If you type the cell references, Excel will still copy the formula to the other cells automatically. One thing should be clear, however, about formulas that use the column headers: They are much easier to understand Correcting Common Formula Errors Sometimes, when you enter a formula, Excel displays a value that begins with a hash mark (t) This is a signal that the formula is returning an error value. You have to correct the formula (or correct a cell that the formula references) to get rid of the error display. In some cases, Excel won't even let you enter an erroneous formula. For example, the following formula ismissing the closing parenthesis:=AL*(B1+C2 If you attempt to enter this formula, Excel informs you that you have unmatched parentheses, and it proposes a correction. Often, the proposed correction is accurate, but you can’t count on it. Table 1 lists the types of error values that may appear in a cell that has a formula. Formulas may return an error value if a cell to which they refer has an error value. This is known as the ripple effect—a single error value can make its way into lots of other cells that contain formulas that depend on that one cell. Tablet Excel Error Values Error Value Explanation sorv/o! The fonnula is rying to divide by zo. This also occurs when the formula atiernps to divide by what's in a cell hat serpy hati, by nothing), AME? The formula uses a name that Excel doesn't recognize, Tis can happen if you delete 2 name that sed in Gre feels oil you have nested quotes ben wings ton aA The formula i referring \cneetly or indieecly to cell that uses the NA function to signal that data renct available Some functions ior example, VLOOKUP) can ala run 41/2 LL The formula uses an intersection of two ranges that ont intersect. (hie concept is clesenbed Inter in the chagser) saa A problem with 2 value exiss for example, you specified a negative number where a postive number is expected, Rue The formula refers © a Cl that sn valid. this can Rappen ithe cell has been deleted fram the wworkshees vanes “The formula includes an argument or operand ofthe wrong ‘ype. An aperandis a value o cell reference that a formula uses to calculate a resultUsing Advanced Naming Techniques Using range names can make your formulas easier to understand, easier to modify, and even help prevent errors. It’s much easier to deal with a meaningful name such as Annual Sales than with a range reference such as ABI2:AB68. Using names for constants Many Excel users don’t realize that you can give a name to an item that doesn’t appear in a cell. For example, if formulas in your worksheet use a sales-tax rate, you would probably insert the tax-rate value into a cell and use this cell reference in your formulas. To make things easier, you would probably also rname this cell something similar to SalesTax Here's how to provide a name for a value that doesn’t appear in a cell Choose Formulas *? Defined Names *? Define Name. Excel displays the New Name dialog box. Enter the name (in this case, SalesTax) into the field labeled Name. Select a scope in which the name willbe valid (either the entire workbook ora specific worksheet), Click the Refers To box, delete its contents, and replace the old contents with a value (such as 075). Use the Comment box to provide a comment about the name (optional). 6. Click OK to close the New Name dialog box and create the name.Decision Making through Lookup, Referencing and Nested formulas If-Then-Else & Nested If Using the IF Function for Simple Lookups The IF function is very versatile and is often suitable for simple decision-making problems. The accompanying figure shows a worksheet with student grades in column B, Formulas in column C use the IF function to return text: either Pass (a score of 65 or higher) or Fal (a score below 65). For example, the formula in cell C2 is =IF(B2>=65,"Pass",” Student Score Andy Barbara chris Dennis ‘Elsie Francine jo foo |i Jarfur|as loo ln fe WAH] Sheets “EI ‘The basic IF statement ‘The basic format of an IF statement is as follows: =IF(if this is true,returnthis,otherwise return this) Example; +=1F(A2>3,"Greater than three", "Not greater than three") returns the text Greater than three if the number in cell A2 is greater than 3 and Not greater than three, ifitis not. ‘The first argument can be any expression that can be true or false, usually using =,>,<,<=,>= or <>. Other Examples: (A3="X","Yes!,"No") =IF(SUM(A2:A10)>B2, "Over Budget", "Within Budget") ‘(A2<0,0,A2) ‘Combining conditionsCompound If-- All of the above examples include only one condition, but itis possible to combine numerous conditions Using the AND and OR functions. ‘These functions are formatted as follows: ND(Condition 1, Condition 2, Condition 3.....Condition n) =OR(Condition 1, Condition 2, Condition 3.....Condition n) ‘The AND function returns TRUE if ALL of the individual conditions are true. ‘The OR function returns TRUE if ANY of the individual conditions are true. ‘They can be used in IF statements as follows: .00),"In Range"," Out if Range") "X"),"Contains X","Doesn't Contain X") mac eoanthaneds- Ma ed as Home Insect Page Layout Formulas | Dats Review View Developer @ @ o GH SE caesar iesoat ‘ — te 6 recep see fe 3) ts) | 18 FIRE Ba nanci~ BOs time Anderson” 82°40), "MVP" "regula A re ar Name Score 2 Anderson 9 2 smth 7 Johnson 5 6 7 bxample provided by techonthenetcom | Ter WT Shoots Shee SieeS. esey | Nested If More complex decisions (Nested IF Statements). If the decision required is more complicated, you can have IF statements within IF statements - this is called Nesting. Example IFAS ),"NIL",IF(A2>0," POSITIVE", "NEGATIVE")) IF A2 is O, this will return the word NIL, however if A is not 0 the third argument is another IF statement that will return the word POSITIVE if A2 is greater than zero, otherwise it will return NEGATIVE.You can “nest” IF functions to provide even more decision-making ability. This formula, for example, returns one of four strings: Excellent, Very Good, Fair, or Poor. =IF(B2>=90," Excellent”, IF(B2>=70,"Very Good” IF(B2>=50,’Fait”,”Poor”))) Introducing Lookup Formulas Alookup formula essentially returns a value from a table by looking up another related value. A common telephone directory provides a good analogy. If you want to find a person’s telephone number, you first locate the name (look it up) and then retrieve the corresponding number. | use the term table to describe a rectangular range of data. The range does not necessarily need to be an “official” table, as created by Excel's Insert “Tables “Table command Figure shows a simple worksheet that uses several lookup formulas. This worksheet contains a table of employee data, beginning in row 7. This range is named EmpData. When you enter a last name into cell 2, lookup formulas in D2:G2 retrieve the matching information from the table. The following lookup formulas use the VLOOKUP function: D2 =VLOOKUP (C2, EmpData, 2, FALSE) E2 =VLOOKUP (C2, FmpData,3, FALSE) F2 =VLOOKUP (C2, EmpData, 4, FALSE) @ =VLOOKUP (C2, EmpData,5, FALSE) 1 ETT 2 Entera name > 4/5/2005, 3 4 6 Last Name oT 7 ‘allen 3255, aysfio9a |= 8 ker Naney Operations a2 4/16/2003, 3 ‘aunnel ken esrkating 42 2/4fz008 0 Charles Leey ‘Administration zee 9/16/1899 a crameen Moe AAdmnistation na 3/iz/2001 2 awe rite administation 2s0t__a/1s/2005, 3 unwell James Operations 392 24/5/2000 u lis Pamele Data Processing nia 3/24/2000 & Endow td ata Processing 1102 11/12/2003 16 14-49-41 intro example“ veokwo “ookue “balun “match dex. comer. a Figure 7 Lookup formulas in row 2 look up the information for the employee name in cell C2. This particular example uses four formulas to return information from the EmpData range. In many cases, you want only a single value from the table, so use only one formula.