Referencing - Relative or Absolute.

[Tomred]

It's been a long time since I used Excel, please help.

What I Remember
1. Relative Referencing - Copy and Pasting formulae causes the errors because the reference move too.
2. Absolute Referencing - Copy and pasting formulae works fine because the references remain the same.
3. A Dollar sign can be place in front of a relative reference to convert the reference to an absolute reference.
i.e C2 to $C$2

Question 1
Why would I choose to use Relative Referencing if Absolute Referencing (R1C1) is more versatile?

Question 2
If a spreadsheet has been created using Relative Referencing, can It be switched to R1C1 Referencing?

Question 3
If the results of a formula are to be used over several worksheets, I assume Absolute Referencing to
be the way to go. Any advice ?

Thanks

 

[RoryA]

You use relative references if you want a formula to adjust when you copy/fill it across/down, and absolute referencing if you don't. Neither one is inherently better than the other; it all depends on context.

R1C1 has nothing to do with absolute/relative. Reference styles are either R1C1 or A1, and that can be toggled in Excel options. Both styles have absolute and relative reference forms.

 

[Tomred]

RoryA,

Thanks for clarifying that both R1C1 and A1 use both absolute / relative references.
Can you advise a little for me the advantages / disadvantages of R1C1 to A1 referencing and vice versa?

 

[RoryA]

I've never seen anyone actually use Excel with R1C1 references turned on. I certainly never have. The only time I use that reference style is sometimes when adding formulas to ranges in code.

 

[Tomred]

On Page 159 of Excel 2007 theres a topic on using R1C1 to understand formula copying.

A1 referencing may be the mostly used but that might be the only reason R1C1 wasn't promoted the same.
I'd like to better understand a comparison of the advantages / disadvantages of
R1C1 vs A1 referencing and visa versa, so I can make an educated decision which to use.

 

[MARK858]

R1C1 vs A1 referencing and visa versa, so I can make an educated decision which to use.

If I said cell R433C102 would you mentally comprehend what cell it was referring to?
Now imagine a formula with 5 cell references in R1C1 style

 

[Tomred]

If I said cell R433C102 would you mentally comprehend what cell it was referring to?
Now imagine a formula with 5 cell references in R1C1 style

I assume it means Row 433 Column102.

The Mr Excel 2007 book encourages the reader to investigate R1C1 referencing, all be it with respect to formula copying.

I was under the impression there's a reason for the encouragement, other than Copying Formula?

 

[MARK858]

I assume it means Row 433 Column102.

Yes, but can you visualize where that cell is in relation to others for example can you visualize what cells the formula below is referring to easily

=((R433C102/R764C28)*R3C2)+R76C27

 

[Tomred]

Yes, but can you visualize where that cell is in relation to others for example can you visualize what cells the formula below is referring to easily

=((R433C102/R764C28)*R3C2)+R76C27

Looking at them, It's just like a form of coordinates.

As I said earlier, I thought the book was encouraging the reader to investigate R1C1 Referencing.
Your responses suggest otherwise.

 

[MARK858]

Looking at them, It's just like a form of coordinates.

As I said earlier, I thought the book was encouraging the reader to investigate R1C1 Referencing.
Your responses suggest otherwise.

As Rory has stated there are certain circumstances where it is easier to work with R1C1 style (especially in VBA) and you should learn how they work but in general the human mind comprehends A1 style easier than R1C1 style.

BTW, the formula in my last post in A1 style is below, which I comprehend what is happening easier

=(($CX$433/$AB$764)*$B$3)+$AA$76