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

Bug #306602 reported by gkornelson on 2008-12-09
36
This bug affects 4 people
Affects Status Importance Assigned to Milestone
OpenOffice
In Progress
Unknown
openoffice.org (Ubuntu)
Low
Unassigned

Bug Description

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.

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

Changed in openoffice.org:
status: New → Incomplete
gkornelson (gkornelson) wrote :

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.

Changed in openoffice:
status: Unknown → In Progress
Alan Hoffman (alan426) wrote :

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) wrote :
  • 123.ods Edit (7.4 KiB, application/vnd.oasis.opendocument.spreadsheet)

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.

Chris Cheney (ccheney) on 2009-02-04
Changed in openoffice.org:
importance: Undecided → Low
status: Incomplete → Triaged
boreal123 (svannay) wrote :

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) wrote :

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) wrote :

@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) wrote :

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) wrote :

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.

:)

Changed in openoffice.org (Ubuntu):
status: Triaged → Won't Fix

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

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Duplicates of this bug

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.