Apache OpenOffice (AOO) Bugzilla – Issue 96803
"=" doesnot work correctly
Last modified: 2013-02-12 01:35:33 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.
Created attachment 58451 [details] demo of the described bug
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.
Experimented a bit. You can cheat to get the correct result by input some formula, e.g. "=SUM(B245:B245)". Try it.
Thanx! Tried it... But: Using just any forumular doesn't help: "=SUM(B245:B245)" works, but "=SUM (B245)" has the same error.
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.
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().
Created attachment 59195 [details] multiple operation problem with equal sign
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.
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?
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.
Created attachment 59230 [details] multiple operation problem with equal sign - workaround
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.
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?
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".
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.
I tested a bit: indirect links don't seem to be THE root of the bugs.
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.