https://bugs.documentfoundation.org/show_bug.cgi?id=167829
Bug ID: 167829
Summary: A new addressing syntax
Product: LibreOffice
Version: unspecified
Hardware: All
OS: All
Status: UNCONFIRMED
Severity: enhancement
Priority: medium
Component: Calc
Assignee: [email protected]
Reporter: [email protected]
Description:
Whoever invented the A1 or even R1C1 addressing syntax perhaps made it easy for
getting started or trivial formulas, but hard for anything else.
Let's change it.
Who's going to dare to break with old tradition and do it if not a free &
open-source software like LibreOffice?
The main trouble with the traditional syntax is that it does not allow for
indices that are mathematically computed.
Drawing some inspiration from pyspread
(https://pyspread.gitlab.io/tutorial.html ) and my own musings on this:
Let both row and column indices be numerical, starting either from 1 or 0.
(it does not really matter, but I'd choose from 0 since often in first row/col
have headers)
Sheet index as well.
Let [1,2] mean the address of cell in row 1 and column 2.
More specifically, this will be an absolute address (on relative addressing a
bit later), equivalent to , and replacing the need for, ADDRESS(2,3), which
returns $C$2.
This is the shortest syntax I could think of, hence no letter/name in front,
that allows for any math expression in place of the 2 indices, but also is
readable enough, and kind-of reminiscent of a cells's box boundaries.
Relative addressing: borrowing from pyspread', let r, c, s be "magic variables"
that always stand for the current cell's row, column and sheet index ;
thus r <=> ROW(), c <=> COLUMN()
(alternative letters could be: x,y,z (just not capital; please keep it easy to
type), or i,j,k (usual math notation in linear algebra) )
Thus, for a cell with address 1,2,3, (meaning in third's sheet), =r+c+s will
return 6;
[r-1,c,s] means the value of the cell in the previous row, same column and
sheet.
If you ommit s, means current sheet: [r-1,c].
By extension, if you drop c as well, means current column, thus =[r-1] refers
to the cell in previous row, same column as the cell where the formula resides.
Can drop ommit row too: [,5] will mean same as [r,5], a mixed relative-absolute
reference.
There will also be the opportunity to accept ranges of indices, as in: [2:5,
r], meaning all cells in current row at columns 2 to 5. Could allow [2:2:15,
r], specifying a step, etc.
Will Still need a row/column/sheet function, because sometimes need to compute
offsets relative to an absolute address:
=r([1,2,3]) to give 1, =c([1,2,3]) gives 2, s([1,2,3]) gives 3
--------------------------------
Examples and how they compare to A1 syntax:
- Fibonacci sequence in a column:
0;1; =A6+A5
vs:
0;1; =[r-1]+[r-2] ("current value is sum of the previous 2 values")
- maximum of the previous 7 numbers in same row:
=MAX(B7:E7)
vs:
=MAX([r, c-1]:[r, c-7]) or =MAX([,c-1]:[,c-7]) or =MAX([r, c-7 : c-1])
- you probably will never have to use INDEX or OFFSET again:
The following is for selecting every 3rd number in a column range:
=INDEX($monthly.b$2:b$100, 3*(row()-row(J$2))+1,1)
vs
=[3*(r-r([2])+2]
IMHO, at first it may seem harder to specify relative dependency this way, but
it has the big advantage that it matches the meaning of the
dependency/relationship ("previous 2 values " etc) as opposed to making you
stare at the row and column indices every time.
While at that, you will have the opportunity to remove other syntax warts
inherited from Excel and its granparents.
Steps to Reproduce:
Open your spreadsheet...
Actual Results:
.
Expected Results:
..
Reproducible: Always
User Profile Reset: No
Additional Info:
...
--
You are receiving this mail because:
You are the assignee for the bug.
