I’ve seen some rather arrogant comments on an OpenOffice help forum in discussion of circular references that the user shouldn’t be wanting them. I’m hoping I’m not going to get that here as non-converging circular references can be very useful (ie, not using the ‘iterations’ feature necessarily). I’ve used them before, in Excel. I was dismayed to find that neither OpenOffice nor LibreOffice seem to accept them directly, but I’m hoping someone has found a work-around.
As an example, here is my current challenge - which I’m sure would be relevant also to many other people wanting to analyse huge datasets in text files in a simple way: I am working on a spreadsheet to analyse stock market patterns. Rather than displaying graphs in the normal manner, I wish to perform analysis on a large series of data and display summarised information. Now, the files I’m using contain a huge number of records - that is, whereas the normal way of using a spreadsheet is to load all the data in and repeat the formulae for each row, that is very not remotely convenient when you wish to analyse a large number of files, with vast numbers of rows each. It means having a spreadsheet that may be both extremely wide, and excessively long.
So I have a small spreadsheet that within a macro reads a file one record at a time, and places the record in one cell, recalculates the spreadsheet, and then reads the next record until the end. Then I can use normal spreadsheet functions to split the record up, and do whatever processing I need. But what I want is to be able to accumulate values in the spreadsheet with each record I read. I cannot find a way of getting a value to accumulate the relevant values of the new record just read by adding to itself, which is a circular reference.
I could extend the macro massively by doing all the calculations in there and only adding the final values into the spreadsheet at the end of the file - but that would undermine the benefit of using a spreadsheet in the first place: Ease of programming and simpler to modify. If someone has the criticism that I’m using the spreadsheet more like a programming language - Yes, and to be able to use it that way, as I can with Excel, would be a significant enhancement of functionality - ‘A1=A1+B2’ is easy to implement in virtually any other programming language, Excel, and Gnumeric apparently (not a windows spreadsheet though), but not, it would seem, in OO or LibreOffice.
I know there are some database functions as well, but - jeez, I want to do things simply. This would be so simple if only it worked.
Has anyone found a way round this unnecessary and in my view, somewhat short-sighted restriction in these otherwise excellent applications?
(Using latest version