Joined: 31 Aug 2007
|Posted: Sun Feb 01, 2009 10:47 am Post subject: Huge Slowdowns writing into cells, all cause of one formula?
I have finally dicovered the culprit to my troubles. My troubles: When adding anything to cells, it takes a huge amount of time for it to take effect. Something like 10 seconds. I'd expect it to be almost instant. Loading the file takes a 10 second pause too.
My file has 10 tables, and 3 graphs. It is 1MB in size.
The culprit is 1 formula added to about 8000 cells on one table. It looks like
When(Then When(Then When(Then Average()/Value2,Else Value3/Value4)Else "")Else "")
There is no slowdown if the formula looked like this as before
When(Then When(Then When(Then Value1/Value2,Else Value3/Value4)Else "")Else "")
The change is in the "average". It gets build for about 160 cells of those 8000.
I have tried to disable automatic calculation, but adding to cells takes just as long. If i hit F9 (calculate now) it calculates everything instantly. Without the Average, everything works fast for all the 10 tables maybe containing about 70000 cells of formulas.
However im building many other averages in all of my tables, without troubels. Maybe this is a specific case? Or is a line reached?
Can someone explain?
I use OpenOffice 3.0.0.
Edit; I have deleted 8 tables, that is more than half of the file. Yet it is still slow as long as this special "Average" formula is in. At the point i deleted as much i could, i replaced the average with a simple value in all the cells, and it was imidiatly fast again.
Edit2: I have now created a new calc document. I entered 1456 (arbitrary) into Cells K13:CZ100 of table1. Then i have entered "Mittelwert(table1.$K13:L13)" into cell L13 of table2. Copy and pasted this into K13:CZ100. (Mittelwert is the german for average, dont know the equivalent for english calc.)
Inserting this went fast. But any following action is already muchmuch slower. I tried to redo the last action (paste) ,took a few seconds.
Then i entered "Mittelwert(table1.$K13:$CZ13)", and pasted this into cells K13:CZ100. It also took long, but pasting it again(or doing anything else to the document) went instant probably because the first formula in all the cells was gone.
YOu can try this out yourself, its reproducable like that. Maybe faster processors reduce this effect. Now this is an easy document, in my full one the slow effect is much stronger
Sadly only the first formula, with one reference having a $, the other not, is useful here. Otherwise i'd have to enter everything manually.
please reply if you can confirm