# NavList:

## A Community Devoted to the Preservation and Practice of Celestial Navigation and Other Methods of Traditional Wayfinding

**Re: Using Calc.**

**From:**Lu Abel

**Date:**2009 Jan 18, 16:21 -0800

George: I think what you have captured is a number of dilemmas in creating software. First of all, let's remember that Excel is a product created by a private company (Microsoft). They have never published a formal definition of how the software should behave. Calc is an attempt by the open-source community to build an Excel-compatible product. Their chances of success? Excellent that they'll get most of it right, impossible that they'll get all of it right. I'll bet, for example, that the exact meaning of the iterate function is nowhere defined by Microsoft. To me, as a software engineer, iteration is done until (a) the variable that causes the iteration no longer changes or (b) the number of iterations reach some program-defined upper limit. From your example, it appears as if Microsoft has instead defined (a) as "until all variables, whether dependent or independent, no longer change" So even thought A1 is constant, Excel keeps iterating because B1 isn't -- despite the fact that B1 is a dependent variable and not an independent one. Worse, making a product work as a previous version worked -- even if the way the previous version worked was wrong -- is a common requirement for new versions of software (and even hardware). So I'll bet that Calc will never be an exact duplicate of Excel. Very, very close, but never exact, especially in obscure areas like your example where the prototype program's behavior seems wrong. I'd also note that Microsoft's documentation of its software is often poor, incomplete, and/or wrong. You pays your money and you takes your choice.... George Huxtable wrote: > I'm aware that this is a list devoted to navigation rather than programming, > and I hope not too many members will object, if I continue the discussion of > relative merits of different spreadsheet software, namely Excel and Calc. I > am finding it interesting, and it may be instructive to others. > > Both Frank Reed and d walden emphasise the closeness between these two > versions, how Calc emulates Excel, and how a manual, intended for one, can > suffice for the other. All of which is true, but only to a limited extent, > and the user can find himself in a situation, in a less-common application, > where they diverge, and where neither printed manual nor any "help" software > offers a way out of the jam. It can result in hours of wasted time, as I > have found. > > Let me give a simple example. It relates to a problem that faced me a year > or so back. I won't go into the details of that problem, but instead will > boil it down to a simple test, of what Excel can do and which I can't get > Calc to copy. > > Start with an Excel empty spreadsheet, straight "out of the box". My version > is Excel 2000, but my guess is that later versions will behave the same, > compatibly. In the "tools" meno, take this path- > tools>options>calculation , and then tick the "iterations" box and change > "maximum iterations" to 1. Click "OK". Make no other changes. > > In cell A1, put a zero. In cell B1, put =IF(A1<0.5,0,B1+1) That's it. > > Because of the choices made about iterations, the program will now > recalculate only once, when F9 is pressed. When you do so, B1 becomes zero, > just as you would expect from that IF statement, A1 being less than 0.5. You > can press F9 as often you wish, and nothing changes. > > But now, change A1 to contain 1 (and press enter). Now, you're asking the IF > statement to take the other path, and set B1 to equal B1+1 (a "circular > reference", if ever I saw one). If you hadn't limited the iterations, Excel > would object to the circular reference. But instead, it does nothing until > you press F9, when B1 becomes 1. And each time you press F9, B1 increments > by 1. That's exactly what was wanted. > > However, anyone who can get Calc to do the same thing is a better man than I > am. If you manage it, do tell me how. > > George. > > contact George Huxtable, at george@hux.me.uk > or at +44 1865 820222 (from UK, 01865 820222) > or at 1 Sandy Lane, Southmoor, Abingdon, Oxon OX13 5HX, UK. > > > > > --~--~---------~--~----~------------~-------~--~----~ Navigation List archive: www.fer3.com/arc To post, email NavList@fer3.com To unsubscribe, email NavList-unsubscribe@fer3.com -~----------~----~----~----~------~----~------~--~---