Spreadsheet Introduction



Chapter 1.2 - Copying FormulasOne of the major advantages of using an electronic spreadsheet application is the ability to write formulas that can reference other cells. Changes to a value in a cell will automatically affect all other cells with formulas that refer to this value directly or indirectly. Often, it is not only necessary to reuse specific values over and over again, but also to apply the same formula many times to different inputs. For example, consider a spreadsheet where each column contains a series of line item costs such as rent, food, and electricity in rows 5 through 7, and each column represents a different month, in column B January, in column C February, etc. To total rows 5 through 7 in column B, use the formula =B5+B6+B7. To create similar totals in columns C and D use the formulas =C5+C6+C7 and =D5+D6+D7, respectively. The only difference between the three formulas is the column being referenced. Writing these formulas out individually is a tedious, repetitive and time consuming task, especially if there are hundreds of cost categories and dozens of months. Even worse, is the real possibility of at least one typo or mistake! Wouldn’t it be nice if the computer could be told “use the same formula but just change the cell references?” When text is copied in a word processor application, a copy of the selected text is made and then this exact text is pasted in the location indicated. Copying text and values in Excel works exactly the same way. When copying a value such as the number 5, it will be pasted as the number 5. However, when copying a formula containing cell references, Excel will change these references relative to where the formula is being pasted. If the formula =B5+B6+B7 is copied from one column and pasted in the next column to the right, it would become =C5+C6+C7. This allows for use of a “general” formula over and over again, but with respect of a different set of numbers. To understand how cell references change when formulas are copied, we will need to understand cell referencing and how to apply Absolute and Relative cell addressing.Copying cell references in a formulaRelative cell referencingFigure SEQ Figure \* ARABIC 1339979082550Consider the spreadsheet in Figure 1 which summarizes a salesman’s trip expenses. In column B are the costs for travel for the corresponding month; January, February and March. In columns C and D are the corresponding monthly costs for lodging and food. To summarize travel costs over this 3-month period, in cell B7 write the formula =B4+B5+B6. A similar formula will be needed in cell C7 and D7 to total the corresponding costs for lodging and food. Instead of rewriting the formula, it can be copied. If the formula in cell B7 is copied into cell C7, one column to the right of the original formula, the operands (B4,B5,B6) within the formula will change by the same relative distance: one column to the right. This will result in the formula =C4+C5+C6.Cell references such as B4, B5 and B6 are referred to as relative cell references. Relative cell references change when they are copied relative to their displacement. Cell references can also be designated as absolute cell references. Absolute cell references do not change, even when copied into different cells. When copying a formula, if a cell reference is relative, the computer will automatically change that reference relative to where the formula is being moved. Cell references are relative by default; no special characters are required to designate a reference as relative.Examples of how formulas are displaced when copied:What formula will result if the formula =B2+C2/D4 is copied from cell E8 to cell E12? The displacement from E8 to cell E12 is 0 columns and 4 rows. This would be applied to each relative cell reference in the formula. In the first operand B2, column B plus 0 columns would remain column B. Row 2 plus 4 rows would become row 6. So the resulting cell reference would be B6.Similarly, the second operand C2 would become C6 and the third operand D4 would become D8. In cell F12 the resulting formula would be =B6+C6/D8.What formula will result if the formula =B6–C7+2 is copied from cell D9 into cell F12? The displacement from D9 to F12 is 2 columns and 3 rows. This would be applied to each relative cell reference in the formula. In the first operand B6, column B plus 2 columns would become column D. Row 6 plus 3 rows would become row 9. The resulting cell reference would be D9. The second operand C7 would become E10. As the value 2 is a constant, it does not change. The resulting formula in cell F12 would be =D9–E10+2.In the example in Figure 1, when the formula in cell B7 =B4+B5+B6 is copied into cell C7, Excel will automatically adjust all relative cell references in the formula the same number of columns/rows that the formula was moved. The displacement from B7 to C7 is one column and zero rows. Therefore one column and zero rows will be added to each relative cell reference in the formula. B4 plus 1 column and 0 rows is C4, B5 plus 1 column and 0 rows is C5, etc. The resulting formula in cell C7 will be =C4+C5+C6. Constants in formulas (numerical values) remain unchanged in this process.To physically copy a formula in Excel one can use the copy/paste features from Clipboard group on the Home ribbon. A more common method of copying a formula into an adjacent cell or cells is to use the Fill Handle. When the mouse pointer is moved to the lower right hand corner of the active cell it turns the pointer into a plus sign, which is called the fill handle. Once the fill handle appears, hold down the left mouse button and drag it into the desired cell or cells where the formula is to be copied and release. The formula will be automatically pasted into each cell in the selected area.Absolute cell refercing 9937751425575Consider the travel cost spreadsheet in Figure 2. This spreadsheet keeps track of a salesman’s monthly expenditures for travel and lodging, as listed columns B and C respectively. All arrangements for this travel are made by a local travel agent who charges a service fee of 5% of the base price. The worksheet will need to be completed by calculating each of these expenditures including this fee. Can this be done by writing one formula in cell D6 and copying it down two rows and then across one column?Figure SEQ Figure \* ARABIC 2January’s travel cost including the service fee can be calculated by multiplying the base price of $575 (B6) by one plus the service fee of 5% (B1). The resulting value will include the base price plus the additional 5%. Translated into Excel syntax the formula in cell D6 will be =B6*(1+B1). Similar formulas will be required in cells D7 and D8 to calculate the total cost of travel in February and March including the service fee, and in cells E6, E7 and E8 to calculate the costs of lodging. Consider first copying the formula =B6*(1+B1) from cell D6 to cell D7, a displacement of 0 columns and 1 row. The resulting formula would be =B7*(1+B2). Does this give the correct result? No, the new formula multiplies February’s travel costs by the value in cell B2, a blank cell. What is really needed here is to change the travel cost reference relatively (February instead of January), but keep the service fee cell reference the same. Likewise, the formula required in cell E6 to calculate the cost of lodging for January will also require a relative cell reference for the base cost and the value of the service fee to remain unchanged.This can be accomplished by using absolute cell referencing for the cell reference containing the service fee percentage. An absolute cell reference is one that does not change regardless of where it is copied. To indicate that a cell reference is absolute, dollar signs ($) are placed in front of both the column letter and row number of the cell address. The correct formula for cell D6 in the worksheet above would then be =B6*(1+$B$1). When this formula is copied from cell D6 to D7, a displacement of 0 columns and 1 row, it will be changed to =B7*(1+$B$1). The travel cost reference (B7) reflects the displacement while the cost percentage, as it is referenced absolutely, does not change. Likewise, when the formula is copied from D6 to E6 (1 column and 0 rows) it will be changed to =C6*(1+$B$1).Mixed Cell ReferencingIn Figure 3 is another travel cost worksheet. This worksheet summarizes the travel reimbursement for mileage to and from two different field sites over a period of three months. The monthly reimbursement costs are based on the number of trips taken (B5:C7) and the cost per trip. Travel to site A is reimbursed at $125 per trip (D2) and travel to site B at $175 per trip (E2).Figure SEQ Figure \* ARABIC 3-5588085725The formula =B5*D2 can be written in cell D5 to calculate the total reimbursement for January trips to site A. Similar formulas will be needed in cells D6:D7 to calculate reimbusements for February and March to Site A and in cells E5:E7 to calcuate remibusement costs for trips to site B. Can this formula be copied down the column and across the row as written?Consider the following:When copying down, what formula will be needed in cell D6 to calculate the reimbursement costs for site A trips in February? The number of trips is 2 (cell B6) and the price is $125 (D4). The formula should be =B6*D2. If the original formula written in cell D5, =B5*D2, is copied to cell D6, a displacement of 0 columns and 1 row, the resulting formula would be =B6*D3. The reference to the cell containing the number of trips (B6) is correct, but the reference to cell containing the cost per trip for site A (D2) is not. The row in the cell reference D2 should not change.When copying across, what formula will be needed in cell E5 to calculate the reimbursement costs for trips to site B in January? The number of trips is 2 (cell C5) and the price is $175 (E4). The correct formula is =C5*E2. If the original formula written in cell D5, =B5*D2, is copied to cell E5, a displacement of 1 column and 0 rows, the resulting formula would be =C5*E2. Both the reference to cell containing the number of trips (C6) and the reference to cell containing the cost per trip for Site B (E2) are correct. Thus the cell reference for the number of trips changes relatively regardless of whether it is copied down or across, but the cell reference for the cost per trip should only change when copied across but not when copied down. How can Excel be instructed to change only part of a cell reference, the column-but not the row when it is copied? A technique known as mixed (or partial) relative and absolute cell referencing allows part of a cell reference to be absolute by placing an absolute sign ($) in front of only that part of the reference. The formula =B5*D$2 can be written in cell D5 and successfully Figure SEQ Figure \* ARABIC 42868295594995copied both down the column and across the row to calculate the corresponding reimbursement for site. Since there is a $ in front of the row in the reference D$2, the row will not change when the formula is copied. However, since there is no $ in front of the column in D$2, the column will change when copied across. This same technique can be used in a cell reference to hold the column absolute but allow the row to change. Consider the spreadsheet in Figure 4. The orientation of this worksheet is somewhat different than the one in Figure 3. Trip prices are now listed verticalally in cells B3:B5. Instead of costs per month, this worksheet calculates trip costs by department; for the Sales Group and Service Group respectively. To calculate the total costs for the Sales Group’s trips to site A multiply the trip cost to site A (B3) by the number of trips (C3); the corresponding formula is =B3*C3. Again this will need to be copied down the column and across the row to calculate the costs for sites B and C and the Service group. What modifications will be needed to the formula so that the copied formulas will result in correct values for these other sites and the the Service Group?The value in cell B3, $/trip, should vary when the formula is copied down the column to correctly give the $/trip for sites B and C but should not vary when copied across the row to calculate Site A costs for the Service Group. The value in cell C3, #?of trips taken by the Sales group to Site A, should vary both when copied down and across to correctly give the number of trips for the corresponding site and group. Then the only element of the formula that remains the same when copied is the column reference in the operand B3. The final formula that can be placed in cell C3 and copied both down and across is =$B3*C3.Example of how formulas are changed when copied:If the formula =B5+B$5+$B5+$B$5 is copied from cell D1 into cell E3, what formula will result? B5, B$5, $B5, $B$5 all reference the same cell. The difference between these references is in how they will be copied.First determine the number of rows and columns the formula is being moved. In this case, the displacement from column D to E is one column and the displacement from row 1 to 3 is two rows.Apply the displacement of 1 column and 2 rows to each part of each cell reference. B5 is a relative reference, so column B plus 1 column will become column C and row 5 plus 2 rows will become row 7. The new reference is C7. B$5 is relative with respect to the column and absolute with respect to the row. So column B will become C and row $5 will remain the same. The new reference is C$5. $B5 is absolute with respect to the column and relative with respect to the row. So $B will remain the same and 5 will become 7. The new reference will be $B7. $B$5 is absolute with respect to both column and row; there will be no change when the formula is displaced. Combining these references results in the formula =C7+C$5+$B7+$B$5.Copying formulas up & to the left What formula would result if the formula =B2+1 is copied from cell C2 into cell B1? Thus far the examples shown have only displaced formulas to the right and down within the worksheet. Can formulas be copied to rows above and columns to the left of the original formula? Yes. In this example the formula in cell C2 is being displaced minus 1 column and minus 1 row, resulting in the new formula =A1+1.What formula results if =B2+1 is copied from cell C2 into cell A1? The displacement form cell C2 to cell A1 is minus 2 columns and minus 1 row. How can the cell reference B2 be displaced minus 2 columns? There is no such column! In this case, Excel will create the formula =#REF!+1 in cell A1. The value displayed in the cell will be the error message #REF!, indicating an illegal cell reference. Displacing a formula such that the new formula has an invalid row number also results in a #REF! error. If multiple cell references are invalid then a #REF! error will be inserted at each part of the formula where this occurs.Named RangesAnother tool that is provided in Excel is the ability to name a cell or range of cells and later use these names within formulas. When a cell or cells are identified with a name, this is referred to as a Named Range. There are two methods to naming a cell or range of cells:Highlight the cell or cell(s) and type a name in the Name Box which appears in the upper left hand part of the window just to the left of the Formula Bar (Refer to Figure 1 – Chapter 1.1).Use the Name Manger tool in the Defined Names Group on the Formulas Ribbon tab.Named ranges work differently than using cell references when copying formulas. Named ranges are always treated as absolute cell references. They are useful in cases where a cell is required in many formulas and is always addressed absolutely. In such a case, it may be easier to give the cell a name and use that name in your formulas. Recall the spreadsheet used previously, as seen in Figure 5. Figure SEQ Figure \* ARABIC 51050290137795If cell B1 is given the name fee, the formula that was written in cell D6, =B6*$B$1, could be rewritten as =B6*fee. This will make the formula easier to read and understand. Could the cell reference B6 be given a name such as travel_january and used in the formula? This would present a problem when it is copied as B6 must be copied relatively; a named range would not. Named ranges are best used with constants that don’t change or ranges that will be reused over and over again (we will see such examples in subsequent chapters). Error MessagesFor those of you who never type a wrong keystroke, this section may not be of great import. However, for the rest of us, knowing how to read and understand errors message can come in very handy. The error message #REF! was previously introduced in the discussion on copying formulas. It is important to also be aware of several other common error messages so that you can quickly and easily debug your spreadsheets. Listed below are the most common error message symbols. These messages are specific to the Excel application; different spreadsheet applications may use different error message symbols. #####Numeric value too wide to display #DIV/0!Divide by 0 occurs #N/AData being referenced is not available #NAME?Named Range in formula is not recognized#NUM!Problem with a number in a formula or function#REF!Cell reference is not valid#VALUE!Wrong type of argument or operand in a formulaIn subsequent chapters you will see examples of when these error messages are likely to be displayed and how to correct the spreadsheet accordingly.Exercise 1.2-1 – cell addressing: copying formulas using relative & absolute cell addressing1. What new formula will result if the formula in each case is copied from cell B3 to D6?(a) = A1+A2 ____________________________(b) = $A$1+A2 ____________________________(c) = $A1+A2 ____________________________(d) = A$1+A2 ____________________________2. What formula would result if you copied the formula =A1+A2 from cell E7 to cell D9?44786552851153. What value will result in cell B2 if the formula = A1+A2 is copied from cell B3 into cell B2. Use the spreadsheet below for questions 4-7. 1285240165104. What Excel formula can be written in cell C2 that can be copied down the column (C3:C4) to determine the number of candles needed for this child’s birthday cake. You will light one candle per year plus an extra one for good luck. 5. What Excel formula can be written in cell C5 to determine the total number of candles that will be needed for all of the children?6. If you insert a row between Mike and Allison and added another child named Fred who is age 4, how will that affect the value calculated in question 5?7. Write an Excel formula in cell D2 that can be copied down the column to determine the number of candles being lit for this child as a percent of the total candles being used. Exercise 1.2 –2 cell addressing: a Multiplication TableThe above spreadsheet can be created by writing just three formulas and copying them to fill in the entire table. Assume the labels and values in the highlighted cells have already been entered into the spreadsheet.Write an Excel formula in Cell A4 that can be copied down the column into cells A5 through A12 to create a list that increases by one in each successive cell.In cell C2 write an Excel formula that can be copied across the column into cells D2 through K2 to create a list that increases by one in each successive cell.Write a formula in cell B3 to calculate the corresponding value in the multiplication table. Write the formula in such a way that it can be copied both across and down to fill in the entire table – that is, for each cell that the formula is copied into, it calculates the product of the value in column A and the value in row 2.Exercise 1.2 –3 cell Addressing: Chapter review1. (a) Write an Excel formula in cell G6 that can be copied down the column to determine the total value (excluding tax and shipping) of this order. For example, OSU will buy 2000 copies of the Excel book at $13 a copy plus 500 Access books at $15 a copy, plus 2500 PPoint books at $9 a copy. . Be sure to use cell references.1. (b) If the formula you wrote in cell G6 is copied to cell G10, what formula will result? 2. Write a formula in cell H6 that can be copied down the column to determine the sales tax (total * tax rate) for this order. 3. Cell C2 has been named ‘Ship’ and contains the cost per book of shipping. Write an Excel formula in cell I6 that can be copied down the column to determine the cost of shipping. Use the named range in your formula.4. Write an Excel formula in cell J6 which can be copied down the column to determine the Grand Total of this order (total cost of books, tax, and shipping).5. (a) Write an Excel formula in cell K6 which can be copied both down the column and across the row to determine the percentage of each book’s quantity of the total number of books in this order. Hint: divide the number of this type of book by the total number of books in this specific order.5. (b) What formula will result when you copy the formula you wrote in cell K6 when you copy it into cell M10? ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download