What is relatively or absolutely

[Jack-P-Winner]

Can someone give me a real example of the relative and absolute referencing so I can understand the difference?


In Excel formulas, you can refer to other cells either relatively or absolutely. When you copy and paste a formula in Excel, how you create the references within the formula tells Excel what to change in the formula it pastes. The formula can either change the references relative to the cell where you're pasting it (relative reference), or it can always refer to a specific cell. You can also mix relative and absolute references so that, when you move or copy a formula, the row changes but the column does not, or vice versa.
Preceding the row and/or column designators with a dollar sign ($) specifies an absolute reference in Excel.
ExampleComment
=A1Complete relative reference
=$A1The column is absolute; (to what?) the row is relative (to what?)
=A$1The column is relative; (to what?) the row is absolute (to what?)
=$A$1
Complete absolute reference

 

[NARAYANK991]

Hi ,

The terms absolute and relative mean exactly what they do in English ; when Excel copies a formula from one cell to another , the term relative means the relationship between the formula and the source cell.

For instance , suppose you have a formula in a cell J7 , which is =H3 ; what this means is that the cell J7 will contain the data which is present in the cell H3 ; if you now copy this formula to a cell Y31 , how does Excel do the translation ?

It sees that the formula =H3 refers to a cell which is 2 columns less than the formula column ( column J ) , and 4 rows less than the formula row ( row 7 ). Thus to put the correctly translated formula in Y31 , Excel will put in the formula =W27 , since column W is 2 columns less than the destination cell ( column Y ) and 27 is 4 rows less than the destination cell ( row 31 ).

This translation is done in all formulae where the $ symbol is not used before either the column reference , the row reference or both.

Thus , to take the same example , the results in Y31 if the formula in J7 had been different would be :

=$H3 would become =$H27

=H$3 would become =W$3

=$H$3 would become =$H$3

You can figure out how the $ symbol keeps the reference the same , irrespective of where the formula is copied.

Narayan

 

[Jack-P-Winner]

Tanks Narayank! I will read this over a few times until it sinks. I have some errors in my books I am trying to fix so I need to understand the terms

Hi ,

The terms absolute and relative mean exactly what they do in English ; when Excel copies a formula from one cell to another , the term relative means the relationship between the formula and the source cell.

For instance , suppose you have a formula in a cell J7 , which is =H3 ; what this means is that the cell J7 will contain the data which is present in the cell H3 ; if you now copy this formula to a cell Y31 , how does Excel do the translation ?

It sees that the formula =H3 refers to a cell which is 2 columns less than the formula column ( column J ) , and 4 rows less than the formula row ( row 7 ). Thus to put the correctly translated formula in Y31 , Excel will put in the formula =W27 , since column W is 2 columns less than the destination cell ( column Y ) and 27 is 4 rows less than the destination cell ( row 31 ).

This translation is done in all formulae where the $ symbol is not used before either the column reference , the row reference or both.

Thus , to take the same example , the results in Y31 if the formula in J7 had been different would be :

=$H3 would become =$H27

=H$3 would become =W$3

=$H$3 would become =$H$3

You can figure out how the $ symbol keeps the reference the same , irrespective of where the formula is copied.

Narayan

 

[Hui]

Refer: http://chandoo.org/wp/2008/11/04/relative-absolute-references-in-formulas/