[upstream] OOo 3.0 uses comma formula field separators instead of semi-colons

[gkornelson]

Binary package hint: openoffice.org

OpenOffice Calc 3.0 (build 3.0.0-6) from PPA <http://ppa.launchpad.net/openoffice-pkgs/ubuntu> uses commas to separate fields in formulas, rather than the OpenOffice default semi-colon.

After entering a formula for a range of cells using commas, I cannot go back and properly change the Relative/Absolute references of cell references in the formula. If I try to lock just one cell reference and hit Enter, all of the cell references in the formula get locked. Cell references that are "touching" a comma separator will not change at all with Shift-F4.

To get around this problem, I have to enter the formula using semi-colons, adjust the Relative/Absolute references to what I want, and then change the semi-colon back to a comma. If the semi-colon is left in, Calc gives an "Err: 508" in the cell. It also appears to work fine if I leave spaces before and after every comma in the formula.

Calc should be set to use the OpenOffice default of semi-colons for all field separators. This is also the standard for ODF 1.2 documents.

[Chris Cheney (ccheney)]

If I understand the upstream bug report that I have attached this is intended behavior for localization of the way it is entered but should still be stored in the file correctly I presume.

Can you attach an example document that shows the problem you are having and I can make certain whether it is actually intended behavior?

Thanks,

Chris

[gkornelson (gkornelson)]

I won't be able to send this to you until early January, as I am out of town for the Christmas holidays without access to an Ubuntu box. If you'd like to try it yourself, use a function like SUM to add up some numbers in column A, along with one number in column B. The formula would be something like:

=SUM(A1:A5,B1)

The comma is working fine as the field separator in this case, but if you replace it with a semi-colon, you will get an error. That's not the main problem though. The problem is that if you would like to copy this formula down to more cells, adding up more values in column A with cell B1, you have the change the reference to B1 to an absolute reference (Shift-F4). The formula should look like this for this function:

=SUM(A1:A5,$B$1)

However, with the comma separator, OpenOffice is having trouble changing individual references. Selecting the B1 reference in the first formula and pressing Shift-F4 appears to change it properly, but when you hit enter and then re-examine the formula, it doesn't work. If you now replace the comma with a semi-colon, you will be able to properly change all of the individual cell references from relative to absolute and back, but the formula will produce an error once you are finished editing it. Also, if you put a space before and after the comma in the formula, everything works as it should, but this is rather cumbersome to do. The Windows version of OOo 3.0 does not have this problem, as it uses only the semi-colon for all field separators.

[Alan Hoffman (alan426)]

I can confirm this. It's the weirdest thing. I know commas makes formula entry more like Excel, but I kind of like the semicolons.

[shemgp (shemgp)]

I can also confirm this.

Here's one example of how to reproduce it
1. put numbers 1,2,3 in cells A1,A2,and A3 respectively
2. Go to A4 and type =sum(
3. click A1
4. Press CTRL and click A2 (notice that it adds a ; automatically)
5. Press CTRL and click A3
6. Press enter (notice that it displays Err:508)

Also, if i use semicolons to separate parameters, openoffice highlights the cells properly when i try to edit a formula, but if I use semicolons, it doesn't highlight the cells. So I'm left without cell color highlighting when editing formulas, as using semicolons to separate parameters results in an error.

[boreal123 (svannay)]

I also confirm this bug.

The only way I managed to avoid it was to remove OOo from my Intrepid Ibex and install the debs form openoffice.org instead.

[shemgp (shemgp)]

Just upgraded to jaunty-alpha-6 with updates. I can confirm that this is now fixed in openoffice.org-calc 1:3.0.1-5ubuntu2.

When i press CTRL click, it adds a comma instead of a semicolon. Color highlights now work too.

[gkornelson (gkornelson)]

@shemgp:
I think you are refering to a different bug. As of the latest Jaunty updates, this bug is still NOT fixed. You are still not able to edit the relative/absolute cell references of a formula containing commas by pressing Shift-F4 while editing.

[cometdog (ericctharley)]

This is also a problem in Karmic as of today. Haven't checked on it in Lucid yet.

In Karmic, it is not _quite_ as severe. You still can't use Shift+F4 to cycle relative references when you have commas separating arguments. But if you change the commas to semicolons and cycle how you want with Shift+F4, you can hit enter and the formula is accepted by oo.calc; the semicolons are automatically converted to commas. On the other hand, the next time you want to edit the formula you have to manually change all the commas back to semicolons if you want to use Shift+F4 again.

The obvious solution would just be to leave the semicolons alone in the first place...

[clubsoda (clubsoda)]

I think the second issue raised by gkornelson about absolute/relative references is more serious than openoffice-bugs#92056 linked above; so I have posted a fresh report;
http://www.openoffice.org/issues/show_bug.cgi?id=116410
(Hopefully not a duplicate.)

As for the ODF standard; I guess semicolons make sense if you're using commas instead of decimal points; but only if you ignore the element of established practice. I mean; come on; 95% of spreadsheet users separate arguments with commas; don't they? Even the majority of europeans; I'll bet.

:)

[Björn Michaelsen (bjoern-michaelsen)]

[This is an automated message.]
There are no new official OpenOffice.org releases in Ubuntu packaging anymore => Won't Fix

If the problem persists, please mark this bug as "also affects project Libreoffice" or "also affects distribution Libreoffice (Ubuntu)" if that has not happened already.

Please leave references to upstream OpenOffice.org bugs in place to allow cross pollination.