OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Is a more intelligent autocalculating system comming soon?

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
Nylo
General User
General User


Joined: 16 Nov 2006
Posts: 17

PostPosted: Thu Nov 16, 2006 9:23 am    Post subject: Is a more intelligent autocalculating system comming soon? Reply with quote

Hi, I'm a Microsoft Excel user and I use that program a lot, for almost everything, and with time I have learnt most of what can be learnt from that program, including macros. I commonly create and use very complex documents with lots of sheets and formula in them. I believe it is a very powerful and useful tool, maybe the only really good thing that Microsoft has created... BUT, because of all the other crap from Microsoft, especially their operating systems, I am looking for good alternatives to it that can work in other environments.

I had good reports about Open Office so I have installed it and tested it. I have quickly noticed that almost anything that could be done with Excel can also be done with Open Office Calc. Macros need some editting process, but I can deal with that.

However, after some testing with small and easy documents, I tried opening a more complex one I created with Excel. What a disappointment! It took very long to open the document, even after saving it with a format from OO.o, but the worst came when I saw that, even if only changing the background color of a cell without formula and not addressed by any other formula, I would lose control of the program for about five seconds. During that time, the soffice.bin process would use more than 90% of my CPU time. How could it be? After some more testing I discovered that the reason of this is that Open Office Calc was re-calculating every single cell in the document, no matter if they were related or dependent from whatever I was actually changing. And that means a huge ammount of calculations in the documents I use to work with.

Of course, the auto-calculate function can be switched off and then I can force a new calculation any time I need it with F9. But this is rather crap, if you compare it with Microsoft Excel's approach to the problem: no need to calculate the whole workbook, but only the cells that actually depend of whatever change you just made. It is thousands of times more efficient. And I find really surprising that OO.o didn't use this approach too. It's almost the one thing you have to think about first when you create a calculation tool: how to do the calculations efficiently. After all, that's what the program will be used for.

Being this such a clear and obvious lack in Open Office Calc, I would like to know wether this issue is something that programmers keep in mind in orther to correct it in future versions of the application or it is not something I should expect to happen. I'm really eager to find a product good enough to break all of my bonds with Microchof products.

Thanks.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Thu Nov 16, 2006 10:50 am    Post subject: Reply with quote

In general only "dirty cells" are recaculated. Ctrl+Shift+F9 enforces a hard recalculation of all formulas. Your complex Excel doc may have something unusual what "makes Calc believe" there are dirty cells where there aren'tt any. Difficult to tell.
Another general statement from the viewpoint of an advanced user: Yes, Excel is far better than Calc. Calc has some advantages when it comes to conditional formatting. The existence of hierarchically organized cell-styles is an advantage for it's own and Calc can calculate arbitrary cell-styles with function STYLE().
Handling of named references is different. They always belong to the document rather than a sheet, so you can't define the same name differently for two sheets unless you use calculated references (myName = OFFSET($A$1;...)).
myName =$A$1 refers to A1 on any sheet
myName =Sheet2.B2 refers to the cell in next sheet's next row, next column if Sheet1.A1 is the active cell at creation time. Notice the relative sheet-name. I think Excel does not know thedifference between $Sheet1.A1 vs. Sheet1.A1
There is a second type of named references called database-ranges. They always refer to an absolute range and save sorting, filtering and consolidation of this range and it's datasource, if it is an imported range. So you can call Menu:Data>Refresh from the referred range or a single cell within. However, Calc does not support cell-based parameter-queries on datasources. I have posted a macro in the snippets-forum, which seems to work for one user at least.
I do not use iterations, but they are reported to behave differently (less usable).
I often use array-formulas. They have improved very much since version 1, but still you can not use every cell-function in array-context. Sometimes you need a helper range.
Calc does not support dynamic ranges with cell-validation and charts, such as validation-list =OFFSET($Lists.$A$1:$IV$1; MATCH(A1;$Lists.$A$1:$A$1000;0);0;1)
Calc becomes very slow if the document has lots of hard formatting and/or shapes.
The charting module is another weak point, which will be replaced by a new one soon.
Calc does not support R1C1 notation.
Looking for an alternative, you may try Gnumeric. It has more statistical functions than Calc and Excel and it is reported to be as accurate as a spreadsheet can be (there are rounding-issues with any spreadsheet). Beeing a comparatively small application, it is fast, supports all kinds of arrays and dynamicaly calculated ranges and R1C1 notation. It doesn't seem to be scriptable but there are plugins, written in the perl language.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
Nylo
General User
General User


Joined: 16 Nov 2006
Posts: 17

PostPosted: Thu Nov 16, 2006 10:39 pm    Post subject: Reply with quote

Thank you for your help. I'm not completely sure about what you mean with "dirty cells". If you mean cells with formulas or conditional formats that depend on the contents of other cells or changing variables (time, etc), then my document is full of them. But in Excel that has never been a problem because of the different (I would rather say existing) strategy for recalculating cells.

I'm not a programmer, but it can't be so difficult to do. You only need a registry for each cell where you keep a record of what I call "first generation children", i.e. cells that directly reference your cell. Then when you modify the cell, you recalculate only the children, and if the result of a child is different, you also recalculate the children of the child, and so forth until you finish with the list. I really hope it can be implemented in the next version of Open Office, it will make everything quicker.

I may have a look at Gnumeric, but if it doesn't allow scripts I don't think it can replace Excel for me.

Thanks.
Back to top
View user's profile Send private message
noranthon
Super User
Super User


Joined: 07 Jul 2005
Posts: 3318

PostPosted: Fri Nov 17, 2006 12:50 am    Post subject: Reply with quote

One change I would recommend is that you use styles rather than hard formatting whenever practicable. From what you say, auto recalculation was not responsible for the deterioration in performance.

You mention that a format change increased the burden on your system. I do not understand the logistics but I have heard from on high that the way cell formats are organised within Calc can stretch system resources.

You call the Stylist with F11 (Format >Styles and Formatting). I was a reluctant starter but I am convinced now that Styles are a more efficient way to organise formats.
_________________
search forum by month
Back to top
View user's profile Send private message
Nylo
General User
General User


Joined: 16 Nov 2006
Posts: 17

PostPosted: Fri Nov 17, 2006 2:11 am    Post subject: Reply with quote

Thank you for your help. Yes, I have lots of different cell formats in my document and I'm sure that, with Calc, it is not as efficient as using styles. But I am also quite sure that, in this case, the problem was caused by the recalculate option, because as soon as I disabled it the program could perform very quickly.

Maybe it's a mix of the 2 things. Maybe the high ammount of cell formats made the recalculatations take very long. I will go on testing to make sure. But anyway the program shouldn't recalculate things that are unrelated to the change that has just been made. That only means useless CPU usage.

Thanks.
Back to top
View user's profile Send private message
noranthon
Super User
Super User


Joined: 07 Jul 2005
Posts: 3318

PostPosted: Fri Nov 17, 2006 3:36 am    Post subject: Reply with quote

I believe that's what Villeroy means by "dirty cells" - cells affected by changes. I would have thought, also, that automatic recalculation only dealt with cells affected by changes. Perhaps not.
_________________
search forum by month
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Fri Nov 17, 2006 7:44 am    Post subject: Reply with quote

"Dirty Cells".
If I remember correctly, "dirty cells" are used in Excel's VBA (oCell.isDirty or oRange.getDirtyCells or something like that).
Yes, I mean those cells, which need to be recalculated because at least one of their predecessors has changed. As far as I can tell, Calc handles this as smart as any other spreadsheet. Would be interesting why it fails to do so with some particular Excel document.
If I where asked to convert a really complex Excel-file, I would try this path:
Save as ods
Strip all macros into text-files.
Separate all data from calculations
If data are spread across more than one list, make a simple database and import all constant data. Hopefully solving all problems of uniqueness, relations, constancy, validity and size of data-range.
Remove all redundant (copied) formulas
Split all (array-)formulas into tiny steps with as many helper-columns and sheets as required for understanding (or re-think) the entire calculation-model.
Try to replace all macros and cross-table-lookups with smart formulae, auto-formatting, data pilots and database-queries.
Compare results of new model with the original file.
Include conditionally formatted check-cells, indicating problems like values out of expected range.
Finally write some very simple "One-Click-Updates-All-Macros".
Add documentation about the calculation-model, names, db-ranges and macros.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
Nylo
General User
General User


Joined: 16 Nov 2006
Posts: 17

PostPosted: Fri Nov 17, 2006 8:15 am    Post subject: Reply with quote

Villeroy wrote:
In general only "dirty cells" are recaculated



I found this in Calc's Help Menus:
Calc Help wrote:
AutoCalculate
Automatically recalculates all formulas in the document.
All cells are recalculated after a sheet cell has been modified. Any charts in the sheet will also be refreshed.

I want to believe what you say, i.e. that only dirty cells are recalculated, it's the logical thing you can expect from a program like Calc. However in the help menu they write something different, and my sheet takes so long to calculate after just changing the format of a single cell... I'm confused. I will go on testing.

Thanks.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Fri Nov 17, 2006 8:29 am    Post subject: Reply with quote

The help-files are not written by developers.
The API-reference says
http://api.openoffice.org/docs/common/ref/com/sun/star/sheet/XCalculatable.html
Quote:

:: com :: sun :: star :: sheet ::
interface XCalculatable

Base Interfaces

::com::sun::star::uno::XInterface
|
+--XCalculatable

::com::sun::star::uno::XInterface

(referenced interface's summary:)
base interface of all UNO interfaces

Description
represents something that can recalculate.

Methods' Summary
calculate recalculates all dirty cells.
calculateAll recalculates all cells.
isAutomaticCalculationEnabled returns whether automatic calculation is enabled.
enableAutomaticCalculation enables automatic calculation.

[...]

isAutomaticCalculationEnabled
boolean
isAutomaticCalculationEnabled();

Description
returns whether automatic calculation is enabled.

With automatic calculation, each formula cell is recalculated whenever its value is needed after its precedents have changed. The value is needed if the cell is displayed or used in another calculation.
Returns
true , if automatic calculation is enabled.

_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
Nylo
General User
General User


Joined: 16 Nov 2006
Posts: 17

PostPosted: Fri Nov 17, 2006 8:44 am    Post subject: Reply with quote

Thank you, that was very helpful. I will find out what other thing is making my document slow down. I guess it must be related to its macros, as they define some of the functions I use and are not working yet, which leads to error results in many cells. It should stop happening as soon as I rewrite the macros involved. We will see.

Thanks.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Fri Nov 17, 2006 9:05 am    Post subject: Reply with quote

Ah, yes. Possibly some of your VBA-functions still work unless they access the API or some VBA-specific functions. StarBasic is very much slower than VBA. I use some very simple sheet functions for debugging and introspection of few cells like this: http://www.oooforum.org/forum/viewtopic.phtml?t=32909
They demonstrate that non-dirty cells are not recalculated. You have to use Ctr+Shift+F9 with these formulas, because the positional arguments, pointing to a certain cell, do not change. Avoid macros wherever you can. So your docs work with more applications and more users will be able to understand your documents.
An example of Basic-code which may work with Excel and Calc. It makes Excel crash, if the code was saved by Calc as xls. But you can insert this into a new VBA-module.
Code:

Function CrossFoot(Param)
Txt = cStr(Param)
For i = 1 to Len(Txt)
   lResult = lResult + cInt(mid(Txt,i,1))
next
CrossFoot = lResult
End Function


No, I don't expect Excel to fix the crash on import of non-VBA Wink
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group