Building a Spreadsheet Model Cell Addressing in Excel

Building a Spreadsheet Model: Cell Addressing in Excel Objectives: l To understand how formulas are copied l Relative & Absolute Cell Referencing CS&E 1111 Ex. Formulas

The “Power” of using Spreadsheet Applications =B 2*C 2 Copy formula from D 2 to D 3 l l l 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 CS&E 1111 Ex. Formulas

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. CS&E 1111 Ex. Formulas

Relative Addressing In Relative Addressing the cell references in a formula are automatically adjusted “relatively” when you copy the formula into another cell. The resulting formula to depending on the displacement from the original position. CS&E 1111 Ex. Formulas

Relative Addressing- Example: Copy the formula in cell A 3 into cells B 3, A 4 and C 6. What new formulas will result? B 3: col shift = 1; row shift = 0 = B 1+B 2 A 4: col shift = 0; row shift =1 = A 2+A 3 C 6: col shift = 2; row shift =3 = C 4+C 5 CS&E 1111 Ex. Formulas

How can you write a formula in cell D 6 to add 5% to the corresponding base price cost? l l l In D 6 you want to multiply the value 575 by 1. 05 Write the formula = B 6*(1+B 1) What happens when you copy it down and across? CS&E 1111 Ex. Formulas

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 l When copying a term in a formula that has absolute cell referencing, the displacement has no effect on the resulting formula. l CS&E 1111 Ex. Formulas

How can you write a formula in cell D 6 to add 5% to the corresponding base price cost? Use the formula: =B 6*(1+$B$1) Now when this is copied to cell E 8 – a displacement of 1 column and 2 rows the new formula will be: l l l =C 8*(1+$B$1) CS&E 1111 Ex. Formulas

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

Mixed Cell Referencing l l l To reference a cell Absolutely with respect to Row and Relatively with respect to Column Use this notation : D$2 Entry in D 5 is = B 5*D$2. Copy to the following: l l l D 6 is = ? ? E 5 is = ? ? E 7 is = ? ? CS&E 1111 Ex. Formulas

Mixed Cell Referencing To reference a cell Absolutely with respect to Column and Relatively with respect to Row Use this notation : $B 3 Write a formula in cell E 3, which can be copied across the row and down the column, to calculate the total trip expenses for the Sales Group to Site A. CS&E 1111 Ex. Formulas

Example: Write a formula in F 4 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 = E 4/E 2 • 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 ? ? ? CS&E 1111 Ex. Formulas

A Multiplication Table What formula would you write in cell B 2 to calculate the product. This formula must work when copied down the column and across the row to form the 5’s Multiplication table. CS&E 1111 Ex. Formulas

Another convenience in Excel is the ability to Name cells… l l 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. CS&E 1111 Ex. Formulas

Can we use a named range for Service Fee? CS&E 1111 Ex. Formulas

EXCEL ERROR VALUES l l l l ##### #DIV/0! #N/A #NAME? #NUM! - Numeric value too wide to display - Divide by 0 occurs - Data being referenced is not available - Text in formula is not recognized - 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 CS&E 1111 Ex. Formulas

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