R1C1 vs A1 notation, how so say RC[-1] in A1 notation

English
,
#1

Having used R1C1 notation for years I’m having trouble getting my head around A1 notation.
In R1C1 notation I can drop the formula “=RC[-1]” anywhere and have that cell assume the value to the left. Is there an equivalent in A1 notation that doesn’t require the address of either cell being specified?
Perhaps more generally how is relative addressing handled in A1 notation.
Thanks,
Mike

OK I went and read your references so I think I need to restate my question:
In a basic macro how would you define a formula in A1 notation for a cell to equal the value 7 cells to the left as in R1C1 notation “=RC[-7]”.
Thanks,
Mike

1 Like
#2

If you enter in cell B1 the formula =A1, you can copy cell B1 to anywhere, say d45 and it will read =C45. Effectively the same as the formula you gave. This is relative addressing.

Absolute addressing

  • If you want it to always refer to column A then modify the formula to =$A1, so A is absolute.
  • If you want to retain the row but not the column then =A$1 so row 1 is absolute
  • The key F4 cycles through absolute addressing and back to relative
#3

Addresses and References, Absolute and Relative

and more gory details : Apache OpenOffice Community Forum - [Solved] How to reference to cell relative to this cell? - (View topic)

#4

Do you know that there is an option to use R1C1 notation.

image
image866×417 37.9 KB

https://help.libreoffice.org/26.2/en-US/text/shared/optionen/01060900.html?System=WIN&DbPAR=CALC&HID=modules/scalc/ui/optformula/OptFormula#bm_id3149095

#5

Yes, I do , been using it for years, but named ranges won’t use it, so I’m trying to learn A1 notation.
But thanks for the offer even if you don’t know the answer to the question.
Mike

#6

It’s sad that the industry adopted A1 notation. R1C1 was much saner, but MS decided to stick to Lotus’ earlier idea (and everyone else followed the trend). At least there’s an (generally unknown) option :slight_smile:

Well, yes, you got it right: there is no way to have stable formulas in A1. The relative notation depends on the current cell.

My suggestion would be to use R1C1, and convert the formula from R1C1 to A1 in your code using XFormulaParser, and not try to calculate A1 names yourself.

#7

It’s always bad (and sad) if we allow MS (or any “market leader”) to make decisions which factually bind us. Unnegotiated “Industry Standards” are a means of warfare.
However, the "R1C1" addressing was also bad by being too unhandy concerning relative addressing. Entering a relative address should (in most cases) not require to use modifying keys (in specific ALTr as is needed on my keyboard for [ or ]).

It’s also sad that my much better proposition has no chance to get implemented.
Specify r-3c+2 to be used for what R[-3]C[2] is supposed to mean.
The simple change in concept is that unsigned integers are to be interpreted as absolute while signed integers have the relative semantic.
You surely also understand .Sheet37.r5:14c+7:+11 and s+1r4c3 without further explanations.

#11

Which industry standard prohibits a sensible notation for internal use? Even if 99% of the users prefer A1 notation, OASIS had one chance to store formulas in a better notation. From the API point of view, we could fill calculated columns just like this: objRange.setFormula("=(RC1+RC2)/R1C1") and storing the formula in XML would be equally efficient.

#12

I’m not sure what you are trying to say here: do you e.g. imply that someone present here was responsible for OOo’s strategic decision to preserve pre-existing in-file syntax, when creating ODF (which, in turn, was decided by someone, who participated in the industry acceptance, which I called sad)? Or that API was preserved across the file format change?

#13

I don’t think that anybody here was involved. Just regretting a missed chance.

#14

No I don’t. E.g., when your notation tells r+1c+1, I don’t understand if that is “get value in the next row, next column”, or if it’s “get value in the next row, same column, and add 1 to it”.

#15

I think it’s a matter of taste.
I’ve been using Excel for 30 years and always use A1 notation in formulas (although I know counterexamples). On the other hand, R137C84 is good for developing mental arithmetic skills.

1 Like
#16

One way to overcome the A1-problem when creating a calculated column:

Sub fillColumn(oRange, sFml)
    nDown = com.sun.star.sheet.FillDirection.TO_BOTTOM
    nSimple = com.sun.star.sheet.FillMode.SIMPLE
    if isArray(sFml) then
        oRange.getCellRangeByPosition(0,0,uBound(sFml),0).setFormulaArray(Array(sFml))
    else
        oRange.getCellByPosition(0,0).setFormula(sFml)
    end if
    oRange.fillSeries(nDown, nSimple,0,0,0)
End Sub

Pass one column with one formula or a 2d-range with an array of formula strings.

#17

It would have the same meaning as R[1]C[1] in traditional “R1C1 addresing”.
From my pont of view the interger (signed or unsigned) behind the c should be mandatory and the recognition could be left to the parser. An R meaning this row may be handy, but an explicit r+0 would be more clear c+0 or c-0 respectively.

As everybody knows, I’m extremely cooperative. I would even accept rr for “this row” and cc for “this column”. In “A1” column and row are also mandatory, and there is no “this”.

#23

that is horrible cause it’s easy to increment or decrement the row number 1, 2, 3, … but not so the column alphabet A, B, C, …, while in systems of coordinates we are using 2 or 3 numbers P(x|y) or P(x|y|z).

#8

Try this:

' lang:en
' Converts a formula from one syntax to another.
' Parameters:
' oCell      cell for which the formula is specified.
' formula    source formula (may have a leading equal sign).
' fromSyntax syntax of the source formula: 0-Calc; 1-Excel A1; 2-Excel R1C1.
' toSyntax   syntax of the target formula (values ​​as in fromSyntax). Default 0.
Function Formula_Convert(Byval oCell as Object, _
                         Byval formula As String, _
                         Byval fromSyntax As Long, _
                         Optional ByVal toSyntax As Long) As String
   Dim oDoc as Object, oFP as Object, arr, i As Long, isEqualSign as Boolean
   If IsMissing(toSyntax) Then toSyntax = 0
   If Left(formula, 1) = "=" Then
     isEqualSign = True
     formula = Mid(formula, 2)
   End If  
   
   If fromSyntax <> toSyntax Then
     oDoc = oCell.Spreadsheet.DrawPage.Forms.Parent
     oFP = oDoc.createInstance("com.sun.star.sheet.FormulaParser")
     oFP.CompileEnglish = True
          
     oFP.formulaConvention = fromSyntax
     arr=oFP.parseFormula(formula, oCell.CellAddress)
     oFP.formulaConvention = toSyntax
     formula=oFP.printFormula(arr, oCell.CellAddress)
   End If  
   
   Formula_Convert=IIf(isEqualSign, "=", "") & formula
End Function


Sub TestFormula_Convert()
   Dim oCell as Object, sFrom as String, sTo as String
   
   oCell = ThisComponent.Sheets(0).getCellRangeByName("C2")
   sFrom = "=SUM(RC[-1];R3C[1]"
   sTo =  Formula_Convert(oCell, sFrom, 2)  ' convert from RC syntax
   Msgbox "cell: " & oCell.AbsoluteName & Chr(10) & _
          "from: " & sFrom & Chr(10) & _
          "to: " & sTo
End Sub
1 Like
#9

better use com.sun.star.sheet.AddressConvention.XL_R1C1, com.sun.star.sheet.AddressConvention.OOO

1 Like
#10

I have provided a list of possible values ​​in the comment for the fromSyntax parameter.

1 Like
#18

What’s better about AddressConvention and what would Formula_Convert() look like if used?
Thanks,
Mike

#19

@sokol92 already provided the code.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.