relative addressing and absolute addressing

[Bhagya07]

when do we use relative addressing, absolute addressing and mixed addressing ?
can any1 explain with example?

 

[rjwebgraphix]

Relative:
=A1
When copied down it turns to =A2, =A3, =A4 etc
When copied right it turns to =B1, =C1, =D1
When copied diagonally it turns to =B2, =C3, =D4
It's great when copying formulas where the address needs to increase accordingly

Absolute:
=$A$1
No matter where or how you copy it, it will always read =$A$1
This is good when you need its cell reference to stay the same no matter where you copy it.

Mixed:
=$A1
In this case, No matter where you copy it, the reference to column A will always reference column A, but the row reference will increase with each row you copy down.

=A$1
In this case, No matter where you copy it, the reference to row 1 will always reference row 1, but the column reference will increase with each row you copy right.

 

[Bhagya07]

Relative:
=A1
When copied down it turns to =A2, =A3, =A4 etc
When copied right it turns to =B1, =C1, =D1
When copied diagonally it turns to =B2, =C3, =D4
It's great when copying formulas where the address needs to increase accordingly

Absolute:
=$A$1
No matter where or how you copy it, it will always read =$A$1
This is good when you need its cell reference to stay the same no matter where you copy it.

Mixed:
=$A1
In this case, No matter where you copy it, the reference to column A will always reference column A, but the row reference will increase with each row you copy down.

=A$1
In this case, No matter where you copy it, the reference to row 1 will always reference row 1, but the column reference will increase with each row you copy right.

thankyou, and what does the below code means RC[-3]:RC[-1] and Rc[-4]:RC[-2]
ActiveCell.FormulaR1C1 = "=SUM(RC[-3]:RC[-1])"
ActiveCell.FormulaR1C1 = "=AVERAGE(RC[-4]:RC[-2])"

 

[rjwebgraphix]

thankyou, and what does the below code means RC[-3]:RC[-1] and Rc[-4]:RC[-2]
ActiveCell.FormulaR1C1 = "=SUM(RC[-3]:RC[-1])"
ActiveCell.FormulaR1C1 = "=AVERAGE(RC[-4]:RC[-2])"

Got me. I've never had a need for R1C1 formulas. It's obviously summing and averaging a range, but how that range translates into something tangible.... I've never had a need for it. It is the down side of being self-taught/Mr Excel taught, you only learn what you need to know to accomplish the task you're trying to do. I'll see if I can research it sometime today and get back with you though. Unless someone else has an answer for you.

 

[rjwebgraphix]

The response to the follow thread explains what RC[-3]:RC[-1] does.

[Solved] VBA: =sum

In short, it is a way to reference a range that starts 3 columns to the left of the active cell to 1 column to the left of the active cell. It's very similar to the Offset function.