Issue 96803

Summary: "=" doesnot work correctly
Product: Calc Reporter: pfeffer2de <abo>
Component: uiAssignee: AOO issues mailing list <issues>
Status: CONFIRMED --- QA Contact: Rob Weir <robweir>
Severity: Major    
Priority: P3 CC: issues
Version: OOO300m9   
Target Milestone: ---   
Hardware: PC   
OS: All   
Issue Type: DEFECT Latest Confirmation in: 3.4.1
Developer Difficulty: ---
Attachments:
Description Flags
demo of the described bug
none
multiple operation problem with equal sign
none
multiple operation problem with equal sign - workaround none

Description pfeffer2de 2008-12-02 14:12:07 UTC
very strange behavior happens in my .ods file, which seems to be somehow a 
problem of too many multiple operations (german "mehrfachoperation"). If I 
delete some of them (which have no link to the effected cells), everthings 
works fine.

The problem is visible in the attached file:
Goto table "parameter", cell C256 (yellow marked). This cell contains the 
formula "=B245". Press F9 several times: The number shown in the yellow marked 
cell will change (I really don't know why), it should stay to the number shown 
in the referenced cell, e.g. 1000.

Remarks:
cell B245 is referenced by its name "AN_Pausch_Eink", in a multiple operation 
from table "AN" in row 43. The bug doesnot occur if this line is removed. It 
also doesn't occour if all other not referenced cells in table "AN" are removed.

I guess that this bug may be caused by multi-tasking / locking problems. 
Unfortunately I didn't succeed in making a more simple example of a datasheet, 
in which the bug occurs.
Comment 1 pfeffer2de 2008-12-02 14:13:25 UTC
Created attachment 58451 [details]
demo of the described bug
Comment 2 honfui 2009-01-07 00:28:29 UTC
I also encountered similar problem with multiple operations. As described by
pfeffer2de, cell C256 change randomly. Also noticed that cell (C256) also will
change even after closing and opening the spreadsheet.
Comment 3 honfui 2009-01-07 00:43:11 UTC
Experimented a bit. You can cheat to get the correct result by input some
formula, e.g. "=SUM(B245:B245)". Try it. 
Comment 4 pfeffer2de 2009-01-07 01:02:53 UTC
Thanx! 
Tried it... But: 
Using just any forumular doesn't help: "=SUM(B245:B245)" works, but "=SUM
(B245)" has the same error.
Comment 5 honfui 2009-01-07 01:51:58 UTC
In my case, "=SUM(B245:B245) will retain the value in cell C256. 
BUT! Multiple operations will failed! Urrrggh....

So... still no proper work-around.
Comment 6 honfui 2009-01-07 02:39:18 UTC
Do you use any lookup formula, e.g. HLOOKUP, VLOOKUP, MATCH, etc?

My spreadsheet uses lookup functions. Issue #39304 has some problem with
multiple operations when using MATCH().
Comment 7 honfui 2009-01-07 03:21:11 UTC
Created attachment 59195 [details]
multiple operation problem with equal sign
Comment 8 honfui 2009-01-07 03:23:18 UTC
See the attachment for the simplified version of my problem with equal sign when
working with multiple operations. Please note that I have lookup formula in the
spreadsheet. I can't reproduce the problem without lookup formula.
Comment 9 pfeffer2de 2009-01-07 16:12:47 UTC
1. does C256 show the correct nubmer, if sum() references only a cell, not a 
cell as a matrix?

2. I'm not using any lookup function in neither of the entire sheets. But maybe 
the use of names of the cells inspite of A1-syntax causes the same problems?
Comment 10 honfui 2009-01-07 23:44:13 UTC
1. The doc did not say sum() cannot reference to one cell. Worst case make it do
a matrix - for testing sake.

2. Please test and let me know the answer.

BTW, I tried to change the function from lookup() to min() (like yours) in my
sample, but, it seems working correctly.
Comment 11 honfui 2009-01-08 05:20:57 UTC
Created attachment 59230 [details]
multiple operation problem with equal sign - workaround
Comment 12 honfui 2009-01-08 05:30:32 UTC
In the new attachment, I avoided using "=" sign while still using the lookup().
Multiple operations is now OK. So, lookup() is not the culprit. Also noticed
similar problem with Calc ver. 2.4.1.

So, the work around is avoid using "=" sign to reference a cell that is an input
(row/col) for the multiple_operations.
Comment 13 pfeffer2de 2009-01-08 12:15:53 UTC
you are funny: "avoid using equal sign...." without equal sign - what sense 
does multiple operation make?
You mean: always use some function, instead of only the equal sign?
But: if you modify your 'workaround' in the following way: enter "=B4*B3" in 
cell C16, it will still work.
Maybe inderect links to the input fields of multiple operations cause the 
problems?
Comment 14 honfui 2009-01-09 00:12:24 UTC
Sorry for that unreasonable statement. 

How do you differentiate between a direct and indirect link? Is there a way to
demonstrate that?

I put 1 vote for this issue. You may do the same to attract more attention.
Issue status still "UNCONFIRMED". 
Comment 15 honfui 2009-01-09 00:44:50 UTC
I found another problem with MO. It about direct use of the MO row/column input
in the calculation. See issue http://qa.openoffice.org/issues/show_bug.cgi?id=97882.
Comment 16 pfeffer2de 2009-01-09 16:55:21 UTC
I tested a bit: indirect links don't seem to be THE root of the bugs.
Comment 17 Rob Weir 2013-02-12 01:35:33 UTC
Reproduced in AOO 3.4.1.  Load first spreadsheet ("demo of the described bug").  The yellow cell will show different values each time when you hit F9, even though it is only a reference to a static value in another cell.