{"id":55,"date":"2017-07-06T16:03:24","date_gmt":"2017-07-06T16:03:24","guid":{"rendered":"https:\/\/courses.lumenlearning.com\/atd-tc3-spreadsheets\/?post_type=chapter&p=55"},"modified":"2017-07-06T18:21:15","modified_gmt":"2017-07-06T18:21:15","slug":"relative-and-absolute-referencing","status":"publish","type":"chapter","link":"https:\/\/courses.lumenlearning.com\/atd-tc3-spreadsheets\/chapter\/relative-and-absolute-referencing\/","title":{"raw":"Relative and Absolute Referencing","rendered":"Relative and Absolute Referencing"},"content":{"raw":"

Student Learning Outcome:<\/h3>\r\nThe student will understand the difference between Relative and Absolute Referencing and apply this information in an assignment.\r\n\r\nRelative Referencing<\/strong> is what you normally do in a spreadsheet. If you add a column of numbers, (for example), and then pull the formula to the right, you are using a relative reference.\r\n\r\nSee illustration below: By using A1:A4, the formula adds column A: (A1+A2+A3+A4). Pulling the formula to the right into column B automatically changes the column reference letters to \u201cB\u201d without you having to do anything.\r\n\r\n\"\"\r\n\r\nTo \u201cpull\u201d a formula, or move it into the next cell, move your insertion point (the arrow) \u2013 to the bottom right corner of the cell with the formula. There should be a small square box in that corner. (Notice where the arrow is pointing the illustration.) When the arrow turns into a black plus sign, left click and hold down the left mouse. Then drag that box to the right. When you release the mouse, the formula has been placed in the next column and has automatically changed the letters.\r\n\r\n\"\"\r\n\r\nRelative Reference means that the cells are relative \u2013 have a relationship with other cells. The spreadsheet program has created this relationship.\r\n\r\nAbsolute Referencing<\/strong> interrupts the relative referencing so that only chosen cell references can be used.\r\nUsing the dollar sign ($) makes a reference absolute.\r\n\r\nExamples of absolute references.\r\n\r\n1.)=A1 = relative reference\r\n\r\n2.)=$A1 = Column is absolute, row is relative\r\n\r\n3.)=A$1 = Row is absolute, column is relative\r\n\r\n4.)=$A$1 = Everything is absolute\r\n\r\nTRY THIS to understand how it works:\r\n\r\n1. Open a blank spreadsheet and type in the annual Fees as noted in cells A4, A5, and A6.\r\n\r\n\"\"\r\n\r\n2. In cell B4 type the formula as you see it here. (Note: the dollar signs \u2013 which make this reference absolute \u2013 are created by first clicking on cell D2 then pressing the F4 key which is located at the very, very top of the keyboard. There are other keys in this row such as F1 \u2013 which is the help key -, F2, F3, F5, etc. When F4 is pressed, dollar signs should appear before the column reference and before the row reference \u2013 in this case $D$2. (If the F4 key doesn\u2019t appear to work, make sure that the referenced cell (D2) is highlighted. Or you can type the dollar signs by hand.)\r\n\r\n3. Press enter. The amount of 128.125 appears.\r\n\r\n4. Additional Note: Why put in the cell reference C4 two times? This is because you are taking the A4 reference and multiplying it by 25%. Then you adding the original amount back in to get the whole increase amount. You are basically taking 1.25% of the Annual fee amount. (The math is like this: (A4) $102.50* ($D$2).25% (.25) = 25.625. This is the amount of the increase. Then you are adding back in the number you started with \u2013 the original annual fee of 102.50 to determine the total amount of the increase (25.625+102.50 = 128.125.\r\n\r\n5. Pull the formula in B4 down to include B6. Look at the formula carefully. Notice that the Annual fee reference changed because it is relative (or references the next cell). However, the absolute reference (D2) did not change. If the dollar signs were removed, the formula in B5 would be =B5*D3+B5 because it would be relative, meaning it would go to the next cell (D3). The answer would not contain any increase because D3 cell reference is blank.\r\n\r\nCorrect spreadsheet.<\/strong> These are the same spreadsheet with absolute referencing:\r\n\r\n\"\"\"\"\r\n\r\nIncorrect spreadsheet:<\/strong> These are the same spreadsheets without absolute referencing. Compare the formulas.\r\n\r\n\"\"\"\"\r\n\r\n6. In the same worksheet you ae using, change the Increase amount in cell D2 to .35 (35%) Watch the amounts change automatically without pulling down the formula or putting in a different formula.\r\n\r\n\"\"\"\"\r\n\r\n7. If the amount .25 (25%) was used as a number instead of a cell reference, the formula would have to be changed to include the .35 (35%) to include the .35 (35%) in each cell that contained a total using this percent because it could not be done automatically.","rendered":"

Student Learning Outcome:<\/h3>\n

The student will understand the difference between Relative and Absolute Referencing and apply this information in an assignment.<\/p>\n

Relative Referencing<\/strong> is what you normally do in a spreadsheet. If you add a column of numbers, (for example), and then pull the formula to the right, you are using a relative reference.<\/p>\n

See illustration below: By using A1:A4, the formula adds column A: (A1+A2+A3+A4). Pulling the formula to the right into column B automatically changes the column reference letters to \u201cB\u201d without you having to do anything.<\/p>\n

\"\"<\/p>\n

To \u201cpull\u201d a formula, or move it into the next cell, move your insertion point (the arrow) \u2013 to the bottom right corner of the cell with the formula. There should be a small square box in that corner. (Notice where the arrow is pointing the illustration.) When the arrow turns into a black plus sign, left click and hold down the left mouse. Then drag that box to the right. When you release the mouse, the formula has been placed in the next column and has automatically changed the letters.<\/p>\n

\"\"<\/p>\n

Relative Reference means that the cells are relative \u2013 have a relationship with other cells. The spreadsheet program has created this relationship.<\/p>\n

Absolute Referencing<\/strong> interrupts the relative referencing so that only chosen cell references can be used.
\nUsing the dollar sign ($) makes a reference absolute.<\/p>\n

Examples of absolute references.<\/p>\n

1.)=A1 = relative reference<\/p>\n

2.)=$A1 = Column is absolute, row is relative<\/p>\n

3.)=A$1 = Row is absolute, column is relative<\/p>\n

4.)=$A$1 = Everything is absolute<\/p>\n

TRY THIS to understand how it works:<\/p>\n

1. Open a blank spreadsheet and type in the annual Fees as noted in cells A4, A5, and A6.<\/p>\n

\"\"<\/p>\n

2. In cell B4 type the formula as you see it here. (Note: the dollar signs \u2013 which make this reference absolute \u2013 are created by first clicking on cell D2 then pressing the F4 key which is located at the very, very top of the keyboard. There are other keys in this row such as F1 \u2013 which is the help key -, F2, F3, F5, etc. When F4 is pressed, dollar signs should appear before the column reference and before the row reference \u2013 in this case $D$2. (If the F4 key doesn\u2019t appear to work, make sure that the referenced cell (D2) is highlighted. Or you can type the dollar signs by hand.)<\/p>\n

3. Press enter. The amount of 128.125 appears.<\/p>\n

4. Additional Note: Why put in the cell reference C4 two times? This is because you are taking the A4 reference and multiplying it by 25%. Then you adding the original amount back in to get the whole increase amount. You are basically taking 1.25% of the Annual fee amount. (The math is like this: (A4) $102.50* ($D$2).25% (.25) = 25.625. This is the amount of the increase. Then you are adding back in the number you started with \u2013 the original annual fee of 102.50 to determine the total amount of the increase (25.625+102.50 = 128.125.<\/p>\n

5. Pull the formula in B4 down to include B6. Look at the formula carefully. Notice that the Annual fee reference changed because it is relative (or references the next cell). However, the absolute reference (D2) did not change. If the dollar signs were removed, the formula in B5 would be =B5*D3+B5 because it would be relative, meaning it would go to the next cell (D3). The answer would not contain any increase because D3 cell reference is blank.<\/p>\n

Correct spreadsheet.<\/strong> These are the same spreadsheet with absolute referencing:<\/p>\n

\"\"\"\"<\/p>\n

Incorrect spreadsheet:<\/strong> These are the same spreadsheets without absolute referencing. Compare the formulas.<\/p>\n

\"\"\"\"<\/p>\n

6. In the same worksheet you ae using, change the Increase amount in cell D2 to .35 (35%) Watch the amounts change automatically without pulling down the formula or putting in a different formula.<\/p>\n

\"\"\"\"<\/p>\n

7. If the amount .25 (25%) was used as a number instead of a cell reference, the formula would have to be changed to include the .35 (35%) to include the .35 (35%) in each cell that contained a total using this percent because it could not be done automatically.<\/p>\n\n\t\t\t

\n\t\t\t

Candela Citations<\/h3>\n\t\t\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t\t\t\t
CC licensed content, Original<\/div>