Largest size of calc spreadsheet?

[andy-andyo]

Have reached a glass ceiling for the size (and including complexity) of spreadsheet. It is 112MB. I cannot create further rows without it crashing LO. I have another spreadsheet of 128MB which is of different complexity (i.e. different formulas), which is incredibly slow to load. The current 112MB spreadsheet has complex formulas in it to assess a comparison between two columns of figures. Since to spreadsheet is designed to be a thousand cols wide and a million and a half (nearly) rows long I thought it would be better at handling data than this. It has 30 columns and 172811 rows at the moment and I wanted to add at least another 130,000 rows. For the record I have 32GB of ram, a six core Intel processor, nvidia graphics, and 64 GB swap file.

Complex formula: -
The cell formula, of which there are two versions in two columns, searches for and compares the difference of two figures in the two columns going backward in assessment of 22 rows.

I did not think my spreadsheet was particularly big! Has anyone else had this problem and found a solution?

A complex cell formula is: -

=IF(AND((RC[-11]=0),(R[1]C[-11]=“P”),(R[-1]C[-11]=“P”)),(R[1]C[-9])-(R[-1]C[-9]),IF(AND((RC[-11]=0),(R[-1]C[-11]=0),(R[1]C[-11]=“P”),(R[-2]C[-11]=“P”)),(R[1]C[-9])-(R[-2]C[-9]),IF(AND((RC[-11]=0),(R[-1]C[-11]=0),(R[-2]C[-11]=0),(R[1]C[-11]=“P”),(R[-3]C[-11]=“P”)),(R[1]C[-9])-(R[-3]C[-9]),IF(AND((RC[-11]=0),(R[-1]C[-11]=0),(R[-2]C[-11]=0),(R[-3]C[-11]=0),(R[1]C[-11]=“P”),(R[-4]C[-11]=“P”)),(R[1]C[-9])-(R[-4]C[-9]),IF(AND((RC[-11]=0),(R[-1]C[-11]=0),(R[-2]C[-11]=0),(R[-3]C[-11]=0),(R[-4]C[-11]=0),(R[1]C[-11]=“P”),(R[-5]C[-11]=“P”)),(R[1]C[-9])-(R[-5]C[-9]),IF(AND((RC[-11]=0),(R[-1]C[-11]=0),(R[-2]C[-11]=0),(R[-3]C[-11]=0),(R[-4]C[-11]=0),(R[-5]C[-11]=0),(R[1]C[-11]=“P”),(R[-6]C[-11]=“P”)),(R[1]C[-9])-(R[-6]C[-9]),IF(AND((RC[-11]=0),(R[-1]C[-11]=0),(R[-2]C[-11]=0),(R[-3]C[-11]=0),(R[-4]C[-11]=0),(R[-5]C[-11]=0),(R[-6]C[-11]=0),(R[1]C[-11]=“P”),(R[-7]C[-11]=“P”)),(R[1]C[-9])-(R[-7]C[-9]),IF(AND((RC[-11]=0),(R[-1]C[-11]=0),(R[-2]C[-11]=0),(R[-3]C[-11]=0),(R[-4]C[-11]=0),(R[-5]C[-11]=0),(R[-6]C[-11]=0),(R[-7]C[-11]=0),(R[1]C[-11]=“P”),(R[-8]C[-11]=“P”)),(R[1]C[-9])-(R[-8]C[-9]),IF(AND((RC[-11]=0),(R[-1]C[-11]=0),(R[-2]C[-11]=0),(R[-3]C[-11]=0),(R[-4]C[-11]=0),(R[-5]C[-11]=0),(R[-6]C[-11]=0),(R[-7]C[-11]=0),(R[-8]C[-11]=0),(R[1]C[-11]=“P”),(R[-9]C[-11]=“P”)),(R[1]C[-9])-(R[-9]C[-9]),IF(AND((RC[-11]=0),(R[-1]C[-11]=0),(R[-2]C[-11]=0),(R[-3]C[-11]=0),(R[-4]C[-11]=0),(R[-5]C[-11]=0),(R[-6]C[-11]=0),(R[-7]C[-11]=0),(R[-8]C[-11]=0),(R[-9]C[-11]=0),(R[1]C[-11]=“P”),(R[-10]C[-11]=“P”)),(R[1]C[-9])-(R[-10]C[-9]),IF(AND((RC[-11]=0),(R[-1]C[-11]=0),(R[-2]C[-11]=0),(R[-3]C[-11]=0),(R[-4]C[-11]=0),(R[-5]C[-11]=0),(R[-6]C[-11]=0),(R[-7]C[-11]=0),(R[-8]C[-11]=0),(R[-9]C[-11]=0),(R[-10]C[-11]=0),(R[1]C[-11]=“P”),(R[-11]C[-11]=“P”)),(R[1]C[-9])-(R[-11]C[-9]),IF(AND((RC[-11]=0),(R[-1]C[-11]=0),(R[-2]C[-11]=0),(R[-3]C[-11]=0),(R[-4]C[-11]=0),(R[-5]C[-11]=0),(R[-6]C[-11]=0),(R[-7]C[-11]=0),(R[-8]C[-11]=0),(R[-9]C[-11]=0),(R[-10]C[-11]=0),(R[-11]C[-11]=0),(R[1]C[-11]=“P”),(R[-12]C[-11]=“P”)),(R[1]C[-9])-(R[-12]C[-9]),IF(AND((RC[-11]=0),(R[-1]C[-11]=0),(R[-2]C[-11]=0),(R[-3]C[-11]=0),(R[-4]C[-11]=0),(R[-5]C[-11]=0),(R[-6]C[-11]=0),(R[-7]C[-11]=0),(R[-8]C[-11]=0),(R[-9]C[-11]=0),(R[-10]C[-11]=0),(R[-11]C[-11]=0),(R[-12]C[-11]=0),(R[1]C[-11]=“P”),(R[-13]C[-11]=“P”)),(R[1]C[-9])-(R[-13]C[-9]),IF(AND((RC[-11]=0),(R[-1]C[-11]=0),(R[-2]C[-11]=0),(R[-3]C[-11]=0),(R[-4]C[-11]=0),(R[-5]C[-11]=0),(R[-6]C[-11]=0),(R[-7]C[-11]=0),(R[-8]C[-11]=0),(R[-9]C[-11]=0),(R[-10]C[-11]=0),(R[-11]C[-11]=0),(R[-12]C[-11]=0),(R[-13]C[-11]=0),(R[1]C[-11]=“P”),(R[-14]C[-11]=“P”)),(R[1]C[-9])-(R[-14]C[-9]),IF(AND((RC[-11]=0),(R[-1]C[-11]=0),(R[-2]C[-11]=0),(R[-3]C[-11]=0),(R[-4]C[-11]=0),(R[-5]C[-11]=0),(R[-6]C[-11]=0),(R[-7]C[-11]=0),(R[-8]C[-11]=0),(R[-9]C[-11]=0),(R[-10]C[-11]=0),(R[-11]C[-11]=0),(R[-12]C[-11]=0),(R[-13]C[-11]=0),(R[-14]C[-11]=0),(R[1]C[-11]=“P”),(R[-15]C[-11]=“P”)),(R[1]C[-9])-(R[-15]C[-9]),IF(AND((RC[-11]=0),(R[-1]C[-11]=0),(R[-2]C[-11]=0),(R[-3]C[-11]=0),(R[-4]C[-11]=0),(R[-5]C[-11]=0),(R[-6]C[-11]=0),(R[-7]C[-11]=0),(R[-8]C[-11]=0),(R[-9]C[-11]=0),(R[-10]C[-11]=0),(R[-11]C[-11]=0),(R[-12]C[-11]=0),(R[-13]C[-11]=0),(R[-14]C[-11]=0),(R[-15]C[-11]=0),(R[1]C[-11]=“P”),(R[-16]C[-11]=“P”)),(R[1]C[-9])-(R[-16]C[-9]),IF(AND((RC[-11]=0),(R[-1]C[-11]=0),(R[-2]C[-11]=0),(R[-3]C[-11]=0),(R[-4]C[-11]=0),(R[-5]C[-11]=0),(R[-6]C[-11]=0),(R[-7]C[-11]=0),(R[-8]C[-11]=0),(R[-9]C[-11]=0),(R[-10]C[-11]=0),(R[-11]C[-11]=0),(R[-12]C[-11]=0),(R[-13]C[-11]=0),(R[-14]C[-11]=0),(R[-15]C[-11]=0),(R[-16]C[-11]=0),(R[1]C[-11]=“P”),(R[-17]C[-11]=“P”)),(R[1]C[-9])-(R[-17]C[-9]),IF(AND((RC[-11]=0),(R[-1]C[-11]=0),(R[-2]C[-11]=0),(R[-3]C[-11]=0),(R[-4]C[-11]=0),(R[-5]C[-11]=0),(R[-6]C[-11]=0),(R[-7]C[-11]=0),(R[-8]C[-11]=0),(R[-9]C[-11]=0),(R[-10]C[-11]=0),(R[-11]C[-11]=0),(R[-12]C[-11]=0),(R[-13]C[-11]=0),(R[-14]C[-11]=0),(R[-15]C[-11]=0),(R[-16]C[-11]=0),(R[-17]C[-11]=0),(R[1]C[-11]=“P”),(R[-18]C[-11]=“P”)),(R[1]C[-9])-(R[-18]C[-9]),IF(AND((RC[-11]=0),(R[-1]C[-11]=0),(R[-2]C[-11]=0),(R[-3]C[-11]=0),(R[-4]C[-11]=0),(R[-5]C[-11]=0),(R[-6]C[-11]=0),(R[-7]C[-11]=0),(R[-8]C[-11]=0),(R[-9]C[-11]=0),(R[-10]C[-11]=0),(R[-11]C[-11]=0),(R[-12]C[-11]=0),(R[-13]C[-11]=0),(R[-14]C[-11]=0),(R[-15]C[-11]=0),(R[-16]C[-11]=0),(R[-17]C[-11]=0),(R[-18]C[-11]=0),(R[1]C[-11]=“P”),(R[-19]C[-11]=“P”)),(R[1]C[-9])-(R[-19]C[-9]),IF(AND((RC[-11]=0),(R[-1]C[-11]=0),(R[-2]C[-11]=0),(R[-3]C[-11]=0),(R[-4]C[-11]=0),(R[-5]C[-11]=0),(R[-6]C[-11]=0),(R[-7]C[-11]=0),(R[-8]C[-11]=0),(R[-9]C[-11]=0),(R[-10]C[-11]=0),(R[-11]C[-11]=0),(R[-12]C[-11]=0),(R[-13]C[-11]=0),(R[-14]C[-11]=0),(R[-15]C[-11]=0),(R[-16]C[-11]=0),(R[-17]C[-11]=0),(R[-18]C[-11]=0),(R[-19]C[-11]=0),(R[1]C[-11]=“P”),(R[-20]C[-11]=“P”)),(R[1]C[-9])-(R[-20]C[-9]),IF(AND((RC[-11]=0),(R[-1]C[-11]=0),(R[-2]C[-11]=0),(R[-3]C[-11]=0),(R[-4]C[-11]=0),(R[-5]C[-11]=0),(R[-6]C[-11]=0),(R[-7]C[-11]=0),(R[-8]C[-11]=0),(R[-9]C[-11]=0),(R[-10]C[-11]=0),(R[-11]C[-11]=0),(R[-12]C[-11]=0),(R[-13]C[-11]=0),(R[-14]C[-11]=0),(R[-15]C[-11]=0),(R[-16]C[-11]=0),(R[-17]C[-11]=0),(R[-18]C[-11]=0),(R[-19]C[-11]=0),(R[-20]C[-11]=0),(R[1]C[-11]=“P”),(R[-21]C[-11]=“P”)),(R[1]C[-9])-(R[-21]C[-9]),IF(AND((RC[-11]=0),(R[-1]C[-11]=0),(R[-2]C[-11]=0),(R[-3]C[-11]=0),(R[-4]C[-11]=0),(R[-5]C[-11]=0),(R[-6]C[-11]=0),(R[-7]C[-11]=0),(R[-8]C[-11]=0),(R[-9]C[-11]=0),(R[-10]C[-11]=0),(R[-11]C[-11]=0),(R[-12]C[-11]=0),(R[-13]C[-11]=0),(R[-14]C[-11]=0),(R[-15]C[-11]=0),(R[-16]C[-11]=0),(R[-17]C[-11]=0),(R[-18]C[-11]=0),(R[-19]C[-11]=0),(R[-20]C[-11]=0),(R[-21]C[-11]=0),(R[1]C[-11]=“P”),(R[-22]C[-11]=“P”)),(R[1]C[-9])-(R[-22]C[-9]),"")))))))))))))))))))))))

Hi Lupp
As I intimated the formula occurs in two columns basically similar - it assesses the contents of 22 rows prior to its position and identifies one entry in each column - the difference of these values are copied to another column - so the two formulae produce a single result from 484 possible results in the third column. I do not know of anyway of achieving this any other way. Lookup is no good as the data for comparison can be in any of the 22 rows and the area to be examined is a variable itself from 1 to 22 rows (hence the 22 IF statements). I am unable to write a code (macro) for this as I just do not have the knowledge and do not know if it would be possible anyway.

My question continues to be - is there a calculation limit within Calc (regardless of the size of the ram or any other factor). For instance does Calc calculate in parallel or in a series - i.e. the whole sheet in one go simultaneously or row by row or block by block?
andy

I used to use XLS - and of course you will get syntax error as you do not have the information i.e. the column of numbers namely RC[-11].

[Lupp]

… I thought it would be better at handling data than this.

Spreadsheets aren’t mainly made for “handling data” but for calculations.
Your formula simply is an unmaintainable monster of 5585 characters. For what reason did you post it? It basically uses an addressing mode rather uncommon in Calc, and the comma as its parameter separator (not acceptable in locales using the comma as their decimal separator). If I just try to adapt it and to enter it into a cell, I get a syntax error which is obviously hard to find. What do you excpect a user to do who experienced all this? Why do you expect spreadsheet software to accept such formulas?
Tell what you actually want to achieve and ask for a better solution.

[schiavinatto]

Post a template file and whatever you want to answer, it’s easier to try to help.

Edit your question and with the clip icon attach the model.

[Zizi64]

Tips:

• Check the length of the formula string.
• Check the number of the arguments of the AND function.
• And try to compose the formula without RC type references, and without negative RC parameters.

Your formula seems as an inherited formula from the Excel. Use the semicolon separator instead of the comma.

link1

link2

[andy-andyo]

Here’s my own solution - the formula was individually entered in a cell in a column in every row, and as consequence overloaded the spreadsheet and my computers ability / resources to manage the data /calculation.

Entered formula once into a named cell and used the “name” in the rows going down the column in the spreadsheet - the “name”-formula, now in the cell in the row, calls the full formula i.e. my formula above shown (unaltered in the way its written) and now works ok. (all 5000++ characters).

Problem was overloading of resources as suspected, hence my original question, which remains unanswered. But it therefore could make sense to name every formula if it goes beyond using a few characters.

And still there are limits even with names. I have since constructed a spreadsheet for a different problem with 2,500,000 rows. This was managed by spreading the data over 8 separate spreadsheets with about 400,000 rows per sheet. They are slow but work.

Sorry about the delay in answering my own problem from over a year ago.

I always use R1C1 as this is a logical representation of what I’m doing in my spreadsheets where I use logic statements - mainly IF nested statements/formulae. Calc doesn’t seem to have any problem with this. When ever it shows a suggestion for correcting an error it shows commas in the suggestion aswell. Andy