# Largest size of calc spreadsheet?

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].

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

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.

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.

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.