Needing to override 522 error

[Herseem]

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

[mariosv]

Have you tried with one iteration and minimun change to 1E+014, plus hard recalc?

Maybe Pivot table can help, it can take data from a registered data source and does summary operations grouping by fields. If you can attach samples files with data to read and what operations you need to do perhaps someone can help.

[Herseem]

Aha! Setting max iterations to 1 and a high value for minimum change seems to have worked. From an early experiment, it appears to be doing exactly what I intended now. Thank you for that.

For the record, the pivot table suggestion (and thank you for that too) I think would still have required a large number of additional fields next to each row, which is specifically what I was trying to avoid. I’m not just adding up field numbers and values, I’m doing extensive processing on each one first.