Issue 26708

Created attachment 13923 [details]
A simplified version of the spreadsheet which exhibits the behavior

The spreadsheet to which I refer in the original bug report is proprietary and
too large to be attached, so I've created a scaled-down version which has been
submitted.  Unfortunately, this demo has not exhibited the bug for me but it
does represent all of the complexity and functions of the errant spreadsheet.

DISCLAIMER - this demo uses bogus formulas which create worthless values - do
not attempt to use this spreadsheet to design a real truss.

A quick explanation of how the spreadsheet works...

See http://www.marineworks.com/truss.html for a side view of a Pratt Truss
(coincides with the example herein).  A 'panel' spans from one vertical to the
next, the 'nodes' are at the verticals - 11 nodes define 10 panels.

'Setup' provides name/value pairs for some parameters which are used in many
places and may be changed.  Other sheets lookup these values by absolute references.

'Loads 10' (and any other Loads sheets) represent load tables.  Loads are
applied at the nodes (bottom of the verticals).  The Property/Value pairs are
calculated from values in the Loads sheet and the Setup sheet.

'Shapes' represent different structural steel shapes and their properties.  This
table is all static data.

'Calcs 10' (and any other Calcs sheets) are the conclusion of the calculations.
 Cell A2 is the name of the loading sheet.  I can have several loadings for a
truss and switch loadings by just changing the name in this cell.  Notice that
the number of nodes in the Calcs sheet must match the number of nodes in the
Loads sheet.  In this example, each panel contains a top, bottom, vertical, and
diagonal member.  The loading is calculated from the Loads sheet named in A2 and
the lengths are calculated from the Loads sheet and the Setup sheet.  E5:E8
names the default shape from the Shapes sheet for each of the top, bottom,
vertical, and diagonal members - changing one of these cells changes the shape
used for that situation for the entire truss.  In truss design, some areas have
higher and lower stresses, so one can modify the members in those locations by
naming a shape in the E column on the row of interest.  For example, the A shape
is the default for the Top members, but placing a K in E17 would make the
spreadsheet use the K shape for the top member in the 2nd panel.

In my main spreadsheet, I was modifying several parameters in the Setup sheet,
the default members in E5:E8, and individual members further down in the E
column.  When it was misbehaving, the odd thing is that the row number (F
column) was updating correctly, but none of the values to its right were updating.

If you've dug into the formulas, you're probably thinking that I'm a little
crazy.  The 'Modules' sheet is not involved in the calculations but serves to
explain why the Calcs sheet is structured such as it is.  This spreadsheet
really has only one module, but in my main sheet I have many.  The default
module would be rows 1-8 in the Modules sheet.  The default module is cut and
pasted to the top of a new Calcs sheet.  I then have many panel modules (such as
rows 10-14 of the Modules sheet) which I can paste into a Calcs sheet to create
any truss configuration desired with different patterns of verticals, diagonals,
and end treatments.  Each module looks above itself in the A column to determine
its node number and can then find its loading from the Loads sheet named in A2.
 The absolute references and INDIRECT() calls are all necessary so any module
can be cut/pasted/relocated/deleted/inserted anywhere and nothing breaks.

It has seemed to me that bigger/more sheets makes the problem worse.  The
formulas I've put into this demo can handle 37 nodes (36 panels).  Tweaking the
loads on the nodes, the master members in E5:E8 of Calcs, and the individual
members in the E column of Calcs is the type of activity that has generated the
no-update problem for me in the past.

Once again, I'll be happy to help in any way possible.

Hi eike,

please have a look at this.

It's not easy to reproduce and I've succeded in doing so only once.

Frank

Frank, bhl0001,

Could someone please enlighten me about what exactly to look at? What cells
don't get updated if I modify exactly which cell? Do I get this right that I
have to change a value in Calcs10.E5:E8, for example?

Thanks
Eike

Eike,

There is no definitive click-here-to-see-the-bug test.  The most-likely place 
for the non-updates is in the F through N columns of a Calcs sheet.  Changing 
values in the following places will affect those columns:
Setup - B column (values will only affect some rows/columns in the target area)
Loads - B column
Calcs - E5:E8 are mandatory values that can be changed, also insert shape values 
(letters A-L in the E column, for instance E11, E12, E13, or E14 ... E17, E18, 
E19, or E20)  Placing a shape value in the E column in row>10 and then later 
deleting that value so that the default value from E5..E8 is used once again has 
been the highest-frequency trigger of the non-updates in my main spreadsheet.

As you're making changes and watching the recalculates flow, occasionally you 
will notice that cells which should have changed did not.  Double-click in the 
non-updated cell, trivially edit the formula (delete and replace the = sign, or 
downcase a function name, or remove and identically replace a portion of the 
formula) and press enter - then the value will update.

I'm so pleased that Frank was able to experience this bug, if only once so far.  
This one is going to be a tough one, but its solution is essential to OOo.  

I've dug around the mailing lists at OOoForum.org and located a couple of 
articles I had remembered.  I believe these reports are more examples of the 
bug.

January 30, 2004: formulae not calculating
http://www.oooforum.org/forum/viewtopic.php?t=5369&highlight=cell+forget

October 19, 2003: What has gone wrong now
http://www.oooforum.org/forum/viewtopic.php?t=3364&highlight=cell+forget

In both cases, notice JohnV's comments about formulas becoming text and changing 
them back by search/replace = for =, which accomplishes a trivial edit and 
reactivates the formula.

bhl0001,

Please note that the "mysterious formula changes to text" thing mentioned in
those forum articles is nothing but the text number format applied to a cell and
then a formula entered into that cell. And Find&Replace '=' works only if in the
mean time another number format than "Text" was applied.

That is not related to a recalculation bug we're trying to get hold of here.
Sigh.. yes, this is probably going to be a tough one.

Eike

The document should be re-evaluated when CWS ``rowlimit'' is integrated, as some
broadcaster/listener implementation changed.

Due to time problems this is re-targeted to OOo later

I have a spreadsheet which reproducibly exhibits the erroneous behavior.  This 
is in OpenOffice 1.1.3 under Windows XP.

Unfortunately it is a somewhat complex financial calculator.  Note that 
iteration (100 steps, .001 min change) must be on.  Open to tab JEH and enter 
1500 into cell L2.  Cell L12 should become 51.85, but it remains at zero.

I can force an update with repeated strokes of control-shift-F9.  Each stroke 
seems to cause one iteration.  After a sufficient number of strokes, the values 
converge to the same values as in column K.

This spreadsheet works perfectly under Microsoft Excel.  Needless to say, random 
unpredicable failures to update cells makes this sheet unusable under OOo.

Created attachment 18787 [details]
Spreadsheet reproduces misbehavior; OpenOffice 1.1.3, Windows XP, 512 MB RAM

Civiltongue,

Please note  that the behavior you encounter with your document is not related
to this issue and dependencies not being recalculated. Your issue is about
iterations handled differently, and seems to be more related to issue 956. You
may attach your document to that issue  as another testcase.

Thanks
Eike

Eicke,

Thanks for your time and attention.  I appreciate the work you do.

>> Please note  that the behavior you encounter with your document is not 
related to this issue and dependencies not being recalculated. Your issue is 
about iterations handled differently, and seems to be more related to issue 
956. <<

I'm not sure that's true.  956 seems to be focused on iteration, setting of 
iteration parameters, "stickiness" of those parameters, which variable's values 
get compared to the specifed epsilon, etc.

My problem is not about iteration; it is that certain cells just don't get 
recalculated.  In the example sheet I provide, the iterations always properly 
run to convergence -- it's just that one single cell that should have 
contributed to the result was never touched.  That's the topic here, in 26708 --
 "some cells do not recalculate."  I think that the iteration in my example is 
incidental.

I'm hoping that by providing a repeatable test case, I've made your job a 
little easier.  As you can imagine, I'm having a lot of trouble trusting my 
Calc results.

--Dave/

Dave,

> My problem is not about iteration; it is that certain cells just don't
> get recalculated.

I may sound nitpicking, but your issue _is_ about iteration. Just enter
the value 1500 twice into L2 and you'll see that L12 recalculates
according to your expectation. That's because a second iteration is done
with the results of the first iteration.

Eike

Eike,

Thanks for your attention.  I won't argue with what you say.  I just want to do 
my best to get the problem properly categorized, hoping that would make it 
easier to fix.  If you've read my comment (Mon Nov 1 15:27:11) and don't agree, 
I defer to your judgment.  Again, I appreciate your work on OOo and have enjoyed 
using the product.

--Dave/

This reminds me of "issue 5242" that I have reported on May 24. 2002. :

------- quote from issue 5242: ------------
... sometimes when I delete one 
row (or more of them), some cells don't get recalculated when I change some 
values they depend on. The calculations are very simple addition and 
multiplication of two cells. I cannot even force recalculation, until I re-
enter the formula in the cell...
-------- end of quote    ----------

That issue has been resolved, now I don't need to open old Excell files no more,
but I still ocasionaly get the same behavior in files created in OOo from
begining. Another solution (apart from re-entering formula) is to close and
re-open the file.

This time I am not able to reproduce bug. 
It appears in all versions I have used (up to version 1.9m47!)

Now, I can live with that, but have seen more than a few people giving up on OOo
because they cannot rely on results they see.

Sinisa,

> This time I am not able to reproduce bug. 
> It appears in all versions I have used (up to version 1.9m47!)

Is your problem still reproducible in a more recent version, 1.9m62 or so?
It may be that it is related to issue 26431, which is fixed now.

Eike

The bugs was not reproducible for Linux , Oo1.1. However,it seems from the 
original report that  formulas do not update when the changes are made to the 
the lower hierarchy of cells that are indirectly included in the formula. 

Reset assigne to the default "issues@openoffice.apache.org".