Calc 5.0.3.2 AutoCalculate

users
#1

LO 5.0.3.2 Calc (Linux)

I turned AutoCalculate off (Alt+T, N, C) but LO takes the same four minutes (plus or minus five seconds) to process a changed value in one cell as it took with AutoCalculate turned on. Does that make sense? If so, is there any other way to accelerate the entry or alteration of raw data?

FWIW the spreadsheet has ~3,000 rows on each of two sheets and a total of ~300,000 formulas.

#2

This same behavior exists in 4.4.6.3 (Windows)

Also in both cases my Conditional formatting gets turned off when I turn off AutoCalculate. Unexpected behavior.

#3

Hi:

  I've had this kind of problem before. As I understand it is because the
spreadsheet has a lot of formulas into it. Then when you enter a new
value or formula to be calculate, it recalcule all formulas again of the
spredsheet that you are using.

  I only can solve with uncheck autocalculate and continue working
entering new data and when I have enough new information, run
autocalculate to update and review it.

  I don't know if increase the memory improve the result of
autocalculate.

  I think that I had this problem before with different spreadsheet
package from diferent office suites.

Regards, and hope this help,

Jorge Rodríguez

#4

Hi:

I've had this kind of problem before. As I understand it is because the spreadsheet has a lot of formulas into it. Then when you enter a new value or formula to be calculate, it recalculate all formulas again of the spreadsheet that you are using.

I only can solve with uncheck autocalculate

[Thank you Jorge but that's exactly what I reported trying in my original message. It still takes the same four minutes after I enter or revise a single value before it lets me do anything more _and_ the hiding of conditional formatting when AutoCalculate is unchecked is another _unexpected_and_undesirable_ behavior. Can anybody else contribute a solution to my problem? -- jl]

and continue working entering new data and when I have enough new information, run autocalculate to update and review it.

[Actually I intended to use F9 for this but maybe that's what you mean. -- jl]

I don't know if increase the memory improve the result of autocalculate.

[I remember recently seeing someone suggesting a memory tweak but I don't recall what to try in that regard. The Linux version is running on a laptop computer with a solid state disk drive and without crowding memory. According to top,

KiB Mem: 3072748 total, 2961416 used, 111332 free, 540732 buffers
KiB Swap: 7817776 total, 321948 used, 7495828 free, 571112 cached Men

and for soffice.bin top reports

VIRT 1599776 RES 385276 SHR 76000 %CPU 99.9 %MEM 12.5

With AutoCalculate unchecked I reentered the value 2.2 in cell AI1289 and top recorded 3 minutes 59 seconds of CPU time consumed by soffice.bin before that process returned to an idle state.

My point is that AutoCalculate should not even be taking place since I unchecked it but it obviously is.

Once again, I am experiencing this issue in the Windows version of LO 4.4.6.3 _and_ in the Linux version of LO 5.0.3.2. -- jl]

I think that I had this problem before with different spreadsheet package from different office suites.

Regards, and hope this help,

[Again I thank you for trying. Your suggestion obviously worked for you but it's not working for me. -- jl]

Jorge Rodríguez

#5

Hi:

  Excuse me because my words about memory weren't clear. I try to
suggest:

Tools-LibreOffice-Memory

(I'm not sure but it would be)

Regards,

Jorge Rodríguez

#6

Hi James,

sorry but I think not a good news.
https://bugs.documentfoundation.org/show_bug.cgi?id=56896
https://bugs.documentfoundation.org/show_bug.cgi?id=92664

Maybe the problem is in relation with your formulas in the conditional
format, using functions inside CF makes it very slow. I think specially with
the STYLE() function.

Regards.
Miguel Ángel.

#7

Hi James,

sorry but I think not a good news.
https://bugs.documentfoundation.org/show_bug.cgi?id=56896
https://bugs.documentfoundation.org/show_bug.cgi?id=92664

[Thank you for those links. I'm not sure how much they apply to the problem I'm encountering but they are certainly interesting.

I'm not interested in migrating to Excel under any conceivable circumstance. I don't even want to use WPS (the Chinese clone of Excel) though that might be an answer in terms of performance. :frowning:

Does anyone know whether WPS can be run via Wine on Linux? I know it does not play fair with LO on Windows. I don't think programs on Linux through Wine can override system settings. As long as I could control the software update process I think I might trust WPS if it were running on Linux. -- jl]

Maybe the problem is in relation with your formulas in the conditional format, using functions inside CF makes it very slow. I think specially with
the STYLE() function.

[Thank you Miguel.

I do not use _any_ functions in my very simplistic conditional formatting. I have six custom styles defined that differ from the default style in terms only of their background color. They apply to the range Sheet1.AH3:AJ2870. They are activated by six conditions as follows

Condition 1: Formula is $Sheet2.$AP3=1
Condition 2: Formula is $Sheet2.$AP3=2
...
Condition 6: Formula is $Sheet2.$AP3=6

This color codes the background in columns AH through AJ on the first sheet based on any of six specific values or none of them in column AP on the second sheet. Unchecking AutoCalculate turns off this color coding but does not streamline data entry which is taking place in columns AH through AJ on the first sheet. IOW, unchecking AutoCalculate not only fails to accelerate data entry but it also hides visual clues as to what data is supposed to be entered. -- jl]

Regards.
Miguel Ángel.

#8

Hi:

Gnumeric is another free software spreadsheet that has conditional
formatt. It usually comes in the GNU/Linux Distribution repositories.
Sometimes it comes installed.

http://www.gnumeric.org/?ckattempt=1

WPS has installer to Ubuntu at least.

http://wps-community.org/downloads

Regards,

Jorge Rodríguez

#9

Hi:

Gnumeric is another free software spreadsheet that has conditional
formatt. It usually comes in the GNU/Linux Distribution repositories.
Sometimes it comes installed.

http://www.gnumeric.org/?ckattempt=1

WPS has installer to Ubuntu at least.

http://wps-community.org/downloads

Some RPMs and tar also.