Issue 26708 - Formulas occasionally do not recalculate when dependencies change
Summary: Formulas occasionally do not recalculate when dependencies change
Alias: None
Product: Calc
Classification: Application
Component: ui (show other issues)
Version: OOo 1.1
Hardware: PC Linux, all
: P3 Trivial with 7 votes (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
Depends on:
Reported: 2004-03-19 05:34 UTC by bhl0001
Modified: 2017-05-20 11:11 UTC (History)
3 users (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: ---
Developer Difficulty: ---

A simplified version of the spreadsheet which exhibits the behavior (24.10 KB, application/vnd.sun.xml.calc)
2004-03-19 05:35 UTC, bhl0001
no flags Details
Spreadsheet reproduces misbehavior; OpenOffice 1.1.3, Windows XP, 512 MB RAM (38.64 KB, application/vnd.sun.xml.calc)
2004-10-31 22:29 UTC, civiltongue
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description bhl0001 2004-03-19 05:34:09 UTC
System:  RedHat 9.0 with 2.6.1 kernel on Athlon 600 with 384 MB, stock gnome

Application:  Spreadsheet which calculates loads, members, and weights of
structural trusses - moderately elaborate spreadsheet with many references to
other sheets, INDIRECT() calls, and searches in the form of VLOOKUP() and MATCH().

Symptoms:  Occasionally (I've spent all day trying to make a reproducible
demonstration of the problem), some cells do not recalculate when the values on
which they depend have been modified.  It is as though the cell has been dropped
from a dependency map.  If I double-click in the cell and execute a trivial edit
(remove and retype the = sign, change the case of a function from upper to lower
case, etc.) which leaves the formula functionally identical to the original
form, it triggers the spreadsheet to recognize that it has been modified and
that cell will update on a recalculate.  I've experienced entire portions of a
row exhibiting this behavior in regions where all of the equations are local,
relying only on values from columns to its left.  If I trivially edit one cell,
the rest in that row do not update - each cell must be trivially edited followed
by a recalculate.

Other feedback:  I first ran into this problem about a month ago.  When I was
searching the web for answers I found folks writing about cells 'forgetting'
that they held formulas.  One of the answers that brought relief to some users
was to search-and-replace the = signs with = signs.  Those users were
experiencing the very same behavior and this trick marked all of the formulas as
'dirty' and they started to behave as formulas again.  I called two friends
about this problem today and their responses were identical, "I ran into that
too, and I quit using OOo."

Partial solution:  A month ago, I tried an experiment - I turned on iterations
with 100 steps and a delta of 0.001 and turned off autocalculate.  I hadn't
noticed the problem since then, but I believe its frequency had just diminished
to a point where I didn't notice it but it was still occurring.  Last night I
was seeing results that didn't make sense - a closer look revealed that the old
behavior of cells not updating was occurring again.  The trivial edit would coax
the cells back into operation, but the spreadsheet is too large to manually
verify that each cell is updating each time I do a recalculate.

I've been coding for 20 years and I know that bug reports of intermittent, not
reproducible behavior are the worst, but I also believe that this is a very
serious bug that could undermine the credibility of OOo.  I'm willing to lend as
much assistance as possible to the effort.

Many thanks for all of your hard work on a great product.
Comment 1 bhl0001 2004-03-19 05:35:57 UTC
Created attachment 13923 [details]
A simplified version of the spreadsheet which exhibits the behavior
Comment 2 bhl0001 2004-03-19 06:21:46 UTC
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 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.

Comment 3 frank 2004-03-19 10:29:14 UTC
Hi eike,

please have a look at this.

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

Comment 4 ooo 2004-03-19 14:11:56 UTC
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?

Comment 5 bhl0001 2004-03-19 15:13:57 UTC

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.  
Comment 6 bhl0001 2004-03-19 16:53:19 UTC
I've dug around the mailing lists at and located a couple of 
articles I had remembered.  I believe these reports are more examples of the 

January 30, 2004: formulae not calculating

October 19, 2003: What has gone wrong now

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.
Comment 7 ooo 2004-03-19 20:17:29 UTC

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.

Comment 8 ooo 2004-05-04 22:50:11 UTC
The document should be re-evaluated when CWS ``rowlimit'' is integrated, as some
broadcaster/listener implementation changed.
Comment 9 frank 2004-05-05 11:18:50 UTC
Due to time problems this is re-targeted to OOo later
Comment 10 civiltongue 2004-10-31 22:27:06 UTC
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.
Comment 11 civiltongue 2004-10-31 22:29:23 UTC
Created attachment 18787 [details]
Spreadsheet reproduces misbehavior; OpenOffice 1.1.3, Windows XP, 512 MB RAM
Comment 12 ooo 2004-11-01 17:44:03 UTC

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.

Comment 13 civiltongue 2004-11-01 23:27:11 UTC

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 

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.

Comment 14 ooo 2004-11-22 14:36:17 UTC

> 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.

Comment 15 civiltongue 2004-11-23 02:55:33 UTC

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.

Comment 16 sinisa 2004-12-03 07:45:52 UTC
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.
Comment 17 ooo 2004-12-03 10:21:31 UTC

> 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.

Comment 18 vinnikovm 2005-02-04 19:42:29 UTC
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. 
Comment 19 Marcus 2017-05-20 11:11:34 UTC
Reset assigne to the default "".