Calculation takes too long time

[todsj88]

Hello

Calculation (crtl+shift+f9) takes 1.5 hours and I want to reduce this time.

My circumstance:

Windows 10 home / Core I3 / 8g ram

Libreoffice 6.2.4.2 x64 calc with 2 sheets of 8000 lines each

Functions like IF COUNTIF SUMIF VLOOKUP are mostly used

1. I tried 6.4.x.x and 6.4.7.x and 7.x.x.x.
   When I tried 6.4.x.x, calculation was done quite faster but many cells are broken like n/a or other errors and it looks like it stopped during calculation.

2. 7.x.x.x even never got open properly.

3. Removing vlookups didnt help.

4. Of course I dont use auto calculations. I cannot because of too long time.

[Hrbrgr]

Have you tried the detective?

Menü Tools>Detective

Otherwise try to start LibreOffice in Safe Mode

Menü Help>Restart in Safe Mode...

[keme]

Typical cases of resource hungry operations (which will slow down your process) include

• referencing between spreadsheet files
  
  You did not tell us whether your two sheets are two tabbed sheets within the same file or two separate spreadsheet files
• lookups to large unsorted tables
  
  Do you have lookups from each of the 8000 rows looking through the entire table?
• iterative approximation
  
  Do you use circular references?

With some combinations of such operations you easily find your computer calculating for hours.

Please: Keep us from guessing! Help us so we can help you. Show us what you have. If you can’t attach your actual file to your question, at least post the formulas you are using.

[todsj88]

Ebot, thanks for your advice and I tried them already.

Keme, thanks for your request.

I hope these can be answers.

1. They are two tabbed sheets in 1 file
   Referencing each other by vlookups

2. Yes vlookups are searching from each 8000 lines through entire table. As I said I was considering it might be the reason and I removed whole vlookups as trial. It doesnt help. They are all same vlookups in a column to cross-check so I dont need them originally.

3. There is no circular reference. If any exists, libre finds it and shows me an error. This is basic function libre offers. Also I did track them manually as libre offers.

I use long formulas from column P to column EQ with many combined functions.

I hope I was missing something and there will be typical solutions for this because I was searching many web sites including here and I did not succeed.

[keme]

Please: Keep us from guessing! Help us so we can help you. Show us what you have. If you can’t attach your actual file to your question, at least post some formulas you are using.