1 / 17

Building a Spreadsheet Model: Cell Addressing in Excel

Building a Spreadsheet Model: Cell Addressing in Excel. Objectives: To understand how formulas are copied Relative & Absolute Cell Referencing. The “Power” of using Spreadsheet Applications. =B2*C2.

dalton-franks
Download Presentation

Building a Spreadsheet Model: Cell Addressing in Excel

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Building a Spreadsheet Model: Cell Addressing in Excel • Objectives: • To understand how formulas are copied • Relative & Absolute Cell Referencing

  2. The “Power” of using Spreadsheet Applications =B2*C2 • Each entry can be related to other values by including cell referencing in formulas. A formula is a sequence of values and/or cell references. • Formula values are automatically updated when a referenced value changes • Formulas can be copied to perform the same • operations on a corresponding set of data • Charts can be easily generated Copy formula from D2 to D3

  3. Copying Formulas Another Advantage of an Electronic Spreadsheet in the ability to Copy Formulas and re-use them Copy a cell or a range of cells down columns, across rows or both - Use the Copy & Paste features or by using the Fill handle feature. +

  4. Relative Addressing InRelative Addressingthe cell references in a formula are automatically adjusted “relatively” when you copy the formula into another cell. The resulting formula to depending on thedisplacementfrom the original position.

  5. Relative Addressing- Example: Copy the formula in cell A3 into cells B3, A4 and C6. What new formulas will result? • B3:col shift = 1; row shift = 0 • A4:col shift = 0; row shift =1 • C6:col shift = 2; row shift =3 • = B1+B2 • = A2+A3 • = C4+C5

  6. How can you write a formula in cell D6 to add 5% to the corresponding base price cost? • In D6 you want to multiply the value 575 by 1.05 • Write the formula = B6*(1+B1) • What happens when you copy it down and across?

  7. Absolute Addressing • To keep a reference “Absolute ” with respect to both Row and Column use a $ in front of the column and row: $B$1 • When copying a term in a formula that has absolute cell referencing, the displacement has no effect on the resulting formula.

  8. How can you write a formula in cell D6 to add 5% to the corresponding base price cost? • Use the formula: =B6*(1+$B$1) • Now when this is copied to cell E8 – a displacement of 1 column and 2 rows the new formula will be: • =C8*(1+$B$1)

  9. What formula could you write in cell D5 to calculate total cost so that it can be copied both down the column and across the row? • In D5 you want to multiply ‘1’ - #trips to Site A by $125 – $/trip • If you write the formula = B5*D2 what happens when you copy it down and across? What should change in the formula & what should stay the same?

  10. Mixed Cell Referencing • To reference a cell Absolutely with respect to Row and Relatively with respect to Column • Use this notation : D$2 • Entry in D5 is = B5*D$2. Copy to the following: • D6 is = ?? • E5 is = ?? • E7 is = ??

  11. Mixed Cell Referencing To reference a cell Absolutely with respect to Column and Relatively with respect to Row Use this notation : $B3 Write a formula in cell E3, which can be copied across the row and down the column, to calculate the total trip expenses for the Sales Group to Site A.

  12. Example: Write a formula in F4 that can be copied down to calculate the percentage of Blue’s score to the total possible score. • Divide Blue’s score by the total possible points = E4/E2 • When you copy this down you want the person’s score to change to the new person’s score but the total possible points to remain the same • The final formula should be ???

  13. A Multiplication Table What formula would you write in cell B2 to calculate the product. This formula must work when copied down the column and across the row to form the 5’s Multiplication table.

  14. Another convenience in Excel is the ability to Name cells… • Names can be assigned to cells. • Name of a cell is a mapping to its absolute address. • This name can now be used in formulas to refer to that cell. It will be considered an absolute address. • Select the cell you want to name, click on the Name Box, enter name and then press Enter or use the Name Manager tool located on the Formulas Ribbon.

  15. Can we use a named range for Service Fee?

  16. EXCEL ERROR VALUES • ##### - Numeric value too wide to display • #DIV/0! - Divide by 0 occurs • #N/A - Data being referenced is not available • #NAME?- Text 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 formula

  17. Summary In this lecture we discussed how to write and copy formulas specifically addressing the following: • Data precision vs. display • Excel operators and order of precedence • Relative & Absolute cell referencing • Error Messages

More Related