Apache OpenOffice (AOO) Bugzilla – Issue 119565
[From Symphony] The SUM formula result changed incorrectly if save the .xls doc to .ods one.
Last modified: 2012-08-25 14:00:29 UTC
Created attachment 77784 [details] sample file can repro this bug Steps: 1.Launch AOO3.4, open the attached .xls sample file. 2.Save it as a .ods document. 3.Reopen the saved .ods doc, check the formula results in A32008, A32009. Defect: The formula result changed to "#NAME?", actually, it should be "3676".
I am working on this defect. Root cause has been found. It is related to the union operator. AOO use "~" other than ";" as a union operator.
Chart series formula still uses ";" as union operator.
Please have a look at issue 25769. It seems, that it describes the same problem. (In reply to comment #2) > Chart series formula still uses ";" as union operator. The accepted syntax in the fields in the chart data dialog is different from this problem. When improving the Calc DataProvider the issue 64604 should be considered too. Please do not confuse the union operator with a sequence of ranges.
(In reply to comment #3) > Please have a look at issue 25769. It seems, that it describes the same > problem. > > (In reply to comment #2) > > Chart series formula still uses ";" as union operator. > > The accepted syntax in the fields in the chart data dialog is different from > this problem. When improving the Calc DataProvider the issue 64604 should be > considered too. > > Please do not confuse the union operator with a sequence of ranges. Issue 25769 and 64604 focused on core function. Here my focus is MS Office 2003 filter. Below is my root cause analysis and resolution. Please take a look. Root cause: Per ODF version 1.2, union operator has been changed from ";" to "~". But current AOO 2003 import filter for formula still uses ";". This defect caused by MS Office 2003 import. For the "SUM" function in attachment, it seems that the formula is imported correctly in the first step but actually it is not. Because for "SUM" function, the same result will be got by SUM(A1:A2;A3:A4) and SUM(A1:A2~A3:A4). The first one is two parameters. The second one is one parameter with union. Resolution: For range name, cell formula and array formula, use "~" for union when import. For chart series formula, use ";" for union when import. From filter point, chart series formula is the same as other formulas. Here is a chart series formula in MS Office Excel 2003 "=SERIES(,(Sheet1!$A$3:$B$3,Sheet1!$D$3:$E$3),(Sheet1!$A$4:$B$4,Sheet1!$D$4:$E$4),1)". "Sheet1!$A$3:$B$3,Sheet1!$D$3:$E$3" is a union reference.
Created attachment 78077 [details] named sequence of ranges in document from OOo2.4.3 I do not think, that it is a problem of Excel filter. If you want to name a sequence of ranges, that is not possible in AOO3.4. It has been possible in OOo2.4.3 and is still possible in LO3.5. I have not searched yet, when this changed for OpenOffice. You get the same problem, when you open a spreadsheet, which was created with OOo2.4.3 or with LO3.5. Therefore a fix should not be in the Excel filter, but in the core for insert names.
(In reply to comment #5) > Created attachment 78077 [details] > named sequence of ranges in document from OOo2.4.3 > > I do not think, that it is a problem of Excel filter. If you want to name a > sequence of ranges, that is not possible in AOO3.4. It has been possible in > OOo2.4.3 and is still possible in LO3.5. I have not searched yet, when this > changed for OpenOffice. > > You get the same problem, when you open a spreadsheet, which was created > with OOo2.4.3 or with LO3.5. Therefore a fix should not be in the Excel > filter, but in the core for insert names. Now I think your solution is more reasonable. Thanks. Just keep excel filter code. Handle the range name union operator in core function. And this fix has nothing to do with chart sequence of ranges.
confirmed,change its status
Issue clarification: In Excel 2003 define a defined name as "=Sheet1!$D$3:$D$5,Sheet1!$B$3:$B$5". In this defined name, a union operator(Excel uses "," and AOO uses "~") is used to form a union area. This defined name will be passed into a formula SUM. Import them in AOO, by Excel 2003 filter the union operator will be translated into ";" which is an old union operator. And SUM will take it as a parameter separator. So the SUM result seems correct while it is not. In AOO, union operator should be set as "~" in defined name. But fix in Excel 2003 filter is not reasonable. Because: 1. Defined name with union(OO uses ";") created in lower version OO(OOo2.4.3) still cannot be imported correctly even if a fix is patched in Excel 2003 filter. 2. I have checked this formula "=SUM((B3:B5,D3:D5))"(union directly used) created in Excel 2003. It will be imported as "=SUM((B3:B5~D3:D5))" which is correct. While Excel 2003 filter still imports the union as ";". But as last it is "~". Core function must does something which a hint to fix union issue in defined name.
(In reply to comment #8) There seems to be some confusion about the union (or range sequence) operator and named expressions I'd like to clarify. Note: I'll use the ';' semicolon parameter separator for clarity, in Excel that may be different depending on locale, e.g. ',' comma instead. * in ODF the union operator is defined as '~' tilde * OOo, AOOi and LibO use '~' in the UI as well * in Excel, a union is entered as (range1;range2) including the parentheses, without parentheses it is not a union * the Excel form is also accepted by AOOi,... for usability, only that it is transformed to (range1~range2) * old OOo accepted (range1;range2) only (AFAIR) for INDEX() function and treated it specially there, the plain range1;range2 in named expressions passed to functions may have worked by accident * the SUM() function is a bit unlucky example because SUM(range1;range2) and SUM((range1;range2)) and SUM(range1~range2) all yield identical results due to the SUM() function accepting one or more range parameters anyway, which may have led to the assumption that in Excel the union operator would be a simple ';' semicolon * the difference can be seen for example with INDEX((range1;range2);1;1;2) where if the parentheses are omitted the function returns an error because it would get 5 parameters passed instead of 4 * named expressions (AKA defined names) are exactly that, they must define an expression that can be compiled * range1;range2 is NOT an expression * in fact you can't enter that as a named expression, you'd get an error and you also get an error in the imported document if you open the names dialog, select that definition and click the Modify button * range1~range2 or (range1;range2) would be valid expressions * in the imported Excel document the faulty named expression works nevertheless because it is imported as already compiled, as soon as the expression would need to be recompiled it would fail, which is the reason when saved/reloaded as .ods it fails As a solution the Excel import needs to handle that and in named expressions generate a proper union from what looks like a list of ranges instead of passing these as is.