relative vs absolute analogy

[lezawang]

Hello
I am kind of understand what is Relative and Absolute address but I would like a real life analogy that would help to understand the concept. Is there any example? Thank you.

 

[dave2018]

Excel 2016 (Windows) 32 bit
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		1	2	3	4	5	6	7	8	9	10	11	12
2	1	Replace with Formula
3	2
4	3
5	4
6	5
7	6
8	7
9	8
10	9
11	10
12	11
13	12
Sheet1

Copy that table to excel, replace B2 with a formula that multipleis row 1 to column, (in essence, creating a times-table)

The catch is, you can only make 1 formula, then drag it across to the right and down with the fill handle, and it still must calculate properly

 

[dave2018]

Then,

Excel 2016 (Windows) 32 bit
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1		1	2	3	4	5	6	7	8	9	10	11	12		Test
2	1	Replace with Formula
3	2
4	3
5	4
6	5
7	6
8	7
9	8
10	9
11	10
12	11
13	12
Sheet1

Do the same, but add the text in N1 to the beginning of each cell in your formula. for example, B2 should say "Test 1"

 

[lezawang]

Thanks for your reply. I was reading the example below but I still can not relate it to Excel.

https://www.codecademy.com/en/forum_questions/529badc780ff3306c1012ee5

 

[dave2018]

If you are talking about excel, a relative cell address (or refrence) look like this: A1
A absolute refrence looks like this: $A$1

In the first example above, type in =B1*A2

copy it across and down. then, double click random cells. do you see how thats different than if u would have typed: =B$1*$A2 ?

 

[sheetspread]

Thanks for your reply. I was reading the example below but I still can not relate it to Excel.

https://www.codecademy.com/en/forum_questions/529badc780ff3306c1012ee5

In CSS, absolute positioning will keep the page element from moving. Likewise in Excel, the absolute reference will not move/change no matter where you place it on the worksheet. Relative in CSS will move, just like in Excel. The difference there is that in CSS you must tell it where to move (top,left,bottom,right) or it remains static, while in Excel it automatically changes the reference by whatever magnitude/direction you copy the cell as in dave2018's example.

 

[Eric W]

Here's another way to look at it. Say you have a list of prices, and you want to figure out what the tax on each item is at a 7.5% rate. You could put your list in column A. Then in B2, put the formula below:

	A	B	C	D	E	F	G	H	I	J
Cost	Tax	Cost	Tax	Cost	Tax

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"]7.75%[/TD]
[TD="align: right"][/TD]

[TD="align: right"]7.75%[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"] $ 20.00 [/TD]
[TD="align: right"] $ 1.50 [/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ 20.00 [/TD]
[TD="align: right"] $ 1.55 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ 20.00 [/TD]
[TD="align: right"] $ 1.55 [/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"] $ 30.00 [/TD]
[TD="align: right"] $ 2.25 [/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ 30.00 [/TD]
[TD="align: right"] $ - [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ 30.00 [/TD]
[TD="align: right"] $ 2.33 [/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"] $ 25.00 [/TD]
[TD="align: right"] $ 1.88 [/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ 25.00 [/TD]
[TD="align: right"] $ - [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ 25.00 [/TD]
[TD="align: right"] $ 1.94 [/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"] $ 100.00 [/TD]
[TD="align: right"] $ 7.50 [/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ 100.00 [/TD]
[TD="align: right"] $ - [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ 100.00 [/TD]
[TD="align: right"] $ 7.75 [/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"] $ 19.95 [/TD]
[TD="align: right"] $ 1.50 [/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ 19.95 [/TD]
[TD="align: right"] $ - [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ 19.95 [/TD]
[TD="align: right"] $ 1.55 [/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]=A2*7.5%[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]=D2*F1[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I2[/TH]
[TD="align: left"]=H2*J$1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Then copy cell B2, and paste it to B3:B6. Now look at cell B3. The formula becomes =A3*7.5% which is what you want. You don't want it to still say =A2*7.5% because that would give you the same answer as the B2 cell, which we already have. So that's why Excel has relative referencing, so that you can write a formula once, and have it apply to a large range of data.

Now say that the tax rate changes to 7.75%, and you need to change the formulas. You could change the A2 cell again, then paste it down the column again, but that seems like a lot of work, especially if you know the rate may change again in the future. So let's put the tax rate in F1, put F1 in the formula instead of 7.75%, then if it ever changes in the future, we just need to change F1, not all the formulas. So in D put the prices, and in E2 put =D2*F1. Looks good so far, we get $1.55. Now copy E2 and paste down the column. Hey, they're all zero! What gives? Look at the formula in E3. It says =D3*F2. There's nothing in F2, so we get D3*0 = 0. The helpful relative referencing that Excel did for us before is now working against us. How can we tell Excel that we want the D2 reference to change as we paste it to different cells, but keep the F1 cell fixed as we paste it to other cells? In other words, make it an absolute reference?

That's done by putting a $ sign in front of the part that you want to keep fixed. So finally put your prices in H, and =H2*J$1 in I2. Copy I2 and paste down. Now you get the right answers. But if you paste the formula to a different column, you'll get the wrong answer again, because I only fixed the row reference, not the column reference.

It can take a little while to wrap your head around the concept. Sometimes you have to stop and think about which row and/or column you want fixed. Just practice and it will get easier.