Created attachment 150904 [details] A simple example of how cyclic addressing would work, with screengrabs Cyclic addressing would allow users to treat a region of the sheet as if its edges were joined to form a cylinder or torus. It would be useful in mathematical and scientific applications, or wherever cyclic structures exist. Relative addresses offset freely when copied. Absolute addresses do not offset at all. Cyclic addresses would offset cyclically within a limited set of rows, columns or both. How it might work: * Select a rectangle of cells where cyclic addressing is to operate * Specify whether cyclic addressing applies to rows, columns or both Suggested behaviour on copy-paste or replicate-down/across * Relative references to cells within the rectangle are treated as cyclic, i.e. offsets are calculated mod(n) where n is the number of rows or columns (as applicable) * Absolute ($) references operate as usual * Ranges wholly within the rectangle are handled cyclically. A 'reversed' range such as A9:A5 would include cells from A9 to the bottom of the rectangle, and from the top of the rectangle down to A5. * Appropriate behaviour should be defined for ranges extending beyond the rectangle, ranges spanning two or more such rectangles, and range references containing both absolute and relative addresses. PLEASE SEE ATTACHED EXAMPLE Compatibility: earlier versions of LibreOffice should interpret cyclic addresses as normal relative addresses.
We do not only have to take older versions into account but also interoperability with other tools using the open document format (where this feature is not described) and other tools from competitors. I suggest you create a special column for the references and create formula using this values (eg. ADDRESS, INDEX). If you try to do do a moving average you may also use the dedicated function at Data > Statistics.
Thanks Heike for looking at this idea. I totally get the problem with ODF. Obviously ODF has its own update cycle, and new features can, over time, be incorporated. But the ODF standard group (I don't know its formal name)isn't going to mandate a new feature like this one, without substantial support from the office software companies. And I suspect the software companies aren't deluged with user requests for a cyclic cell-addressing mode. So, like a lot of these things, it's chicken and egg. Meanwhile, I'll hope someone invents a time machine, so I can go back to 1978 and have a word with Dan Bricklin and Bob Frankston. If I am successful, you won't notice anything changing. Cyclic addressing will be the way it always was. (And this conversation won't have happened.)