Recently we discussed people’s preference for A1 or R1C1 notation without taking the time to explain how they both work. So here is a bit of an explanation.
For those not familiar with R1C1 notation:
R1C1 means row 1 column 1 ie A1 in the’ other’ notation. Well actually it means $A$1 as its an absolute address. If you copy =R1C1 from any cell to any other it will always to refer to the top left cell (A1).
Relative references in R1C1 are done with square brackets []. so
R[1]C[1] means one cell down and one to the right. In fact to make it easier I did some screen shots. The arrows are show dependents ones. (with hindsight, precedents might have been clearer).
And the A1 equivalents:
Roughly speaking relative references are easier to deal with in A1 style notation and Absolute ones are probably easier to deal with in R1C1 style.
If you haven’t tried using the other style, try it, you may find its useful once you are familiar with it.
Oh and I used an internal tool to do the arrows in one click and PUP to convert from relative to absolute in one click.
HTH, the pictures look better if you click on them.
cheers
Simon
Smurf on Spreadsheets 
Monday, 12th November, 2007 at 5:05 pm |
FWIT
>Oh and I used an internal tool to do the arrows in one click and PUP to >convert from relative to absolute in one click.
http://www.blog.methodsinexcel.co.uk/2006/07/24/auditing-a-whole-range/
and from my own “I’m lazy” collection
Sub RefType()
If Application.ReferenceStyle = xlR1C1 Then
Application.ReferenceStyle = xlA1
Else
Application.ReferenceStyle = xlR1C1
End If
added to a keyboad sc. – save 4 clicks! which equates to 0.03 GBP per year! lol!
Out of interest Simon, what net tool did you use?
Monday, 12th November, 2007 at 5:16 pm |
FWIT?????
meant FWIW
Monday, 12th November, 2007 at 11:48 pm |
Ross
Did you mean F.Wit?
Tuesday, 13th November, 2007 at 1:01 am |
To me it’s the opposite of what Simon wrote: A1 addresses make more sense as absolute references, R1C1 as relative references. First, the only difference between the two for absolute references is the column portion. To me, $X$99 is somewhat more meaningful than R99C24. For relative referencing, if I want the formula in the current cell to be the cell above added to the cell to the left, and if the current cell were X99, the A1 formula would be =X98+W99 while the R1C1 formula would be =R[-1]C+RC[-1]. Note that the latter formula would be exactly the same no matter what the cell contained it, while the comparable A1 formula would change to =D5+C6 if it were supposed to be in cell D6. R1C1 style becomes even more useful for mixed references. To sum the cells from row 3 through the row 2 rows above the cell containing the formula (X99), the formulas would be
A1 =SUM(X$3:X97)
R1C1 =SUM(R3C:R[-2]C)
The A1 formula changes depending on the cell in which it’s entered, but the R1C1 formula remains the same no matter where it’s entered.
It’s far easier to spot inconsistencies in formulas that should be similar if not identical modulo location (e.g., cell X99 containing a formula pasted into X99:AF199) using R1C1 notation than A1 notation, but for me it’s easier to visualize where things are (absolute location) in A1 notation.
Tuesday, 13th November, 2007 at 4:17 am |
Harlan
Sorry , I wasn’t clear. what I meant was the way I have them they are easier to understand (no ugly symbols in the way). In terms of usefulness I agree with you entirely. I was more thinking of people who have never user R1C1 before and didn’t know the []. (it was a search term of what does R[1]C[1] mean that drove me to write the article.)
You’re right I should have been clearer,. My way = easier introduction for a novice, your way = more useful for a more experienced user.
Tuesday, 13th November, 2007 at 9:46 am |
>>Ross
>>Did you mean F.Wit?
Well thats more appt i guess :-)
Friday, 2nd September, 2011 at 5:01 am |
[…] article talks more about styles and formulas (XML Spreadsheets store formulas in relative R1C1 style notation which is far easier to parse than the absolute A1 style notation, which most people […]
Monday, 7th November, 2016 at 11:08 am |
Hi
It might be worth noting , just too help a beginner, that R[1]C, “defaults” to R[1]C[0], which gives the same results and is Relative
Alan