Calc =""+1 shows error #VALUE or err:529

Bug #210153 reported by Christoph Roeder
50
This bug affects 1 person
Affects Status Importance Assigned to Milestone
OpenOffice
Invalid
Undecided
Unassigned
libreoffice (Ubuntu)
Wishlist
Unassigned
openoffice.org (Ubuntu)
Wishlist
Unassigned

Bug Description

Binary package hint: openoffice.org

Since I upgraded to hardy I can't use the spreadsheet from [1] because I get an error 529 in some cells.

The problem is that you can't get the sum of a cell which is empty (""), this worked in OOo 2.3!

I attached a simple example which should show the problem. (the important cells are: A2 and B2)

PS: I use OOo version: 2.4.0rc2-1ubuntu3

[1] http://live.prooo-box.org/templates/Beispiele/PAZE/1.htm

Revision history for this message
Christoph Roeder (brightdroid) wrote :
Revision history for this message
lanzen (lanzen) wrote :

Yeap, I've just seen that, so I can confirm.

It has never happened before in any previous versions.

For the moment I've just replaced a few formulas inserting 0 instead of "", but that's not the expected behaviour, isn't it?

Revision history for this message
Chris Cheney (ccheney) wrote :

Works in upstream openoffice.org 2.4.0

Changed in openoffice.org:
importance: Undecided → High
status: New → In Progress
Chris Cheney (ccheney)
Changed in openoffice.org:
importance: High → Critical
Revision history for this message
Chris Cheney (ccheney) wrote : Re: [ooo-build] [hardy] calc: sum show err:529

This isn't a bug, I talked to Kohei Yoshida about this at GoOOCon. He said that it should be an error and that the next version of OOo will show it is an error also. It also is an error if done in Microsoft Excel and it shows "#VALUE!" and hovering over it says "A value used in the formula is of the wrong data type."

Changed in openoffice:
status: New → Invalid
Changed in openoffice.org:
status: In Progress → Invalid
Revision history for this message
lanzen (lanzen) wrote :

Does this mean it won't change and we all should be pretty happy with it?

Why do I have to change my until now perfectly working since OOo v1 formulas?
I don't know how excel behaves and, really, don't care also.

I wonder...

Revision history for this message
Chris Cheney (ccheney) wrote :

Yes, that is what that means, it was a bug that it worked and it has been fixed in ooo-build in 2.4.0 to now throw an Err:529 and will be fixed in official OpenOffice.org 3.0 to also throw an error.

Chris

Revision history for this message
NoOp (glgxg) wrote :

If this is the correct behavior, then this needs to be documented in the help section (Index: error codes;list of).

Perhaps it's also worthwhile to file an upstream bug as OOo3devM9 does not throw the error.

Revision history for this message
Michael Devenish (mdevenish) wrote :

I've noticed a similar error when opening an excel file in OOo 2.4 (ubuntu). Excel seems to try and convert text to a value automatically when a mathematical formula is applied to a cell which contains a text value.

In the attached sample excel file, the text value "1" in cell A2 is added to the numerical value 2 in cell A3 and the result displayed in cell A4. In excel the value in cell A4 is '3'. In OOo 2.3 the result is '2' since the text value in cell A2 is treated as zero. In OOo 2.4 (ubuntu) the result in cell A4 is 'Err:529'. If the Value() function is applied to the value in cell A2 the result is '3' as expected. The official (sun) builds of OOo 2.4 and OOo3devM7 both display '2' in cell A4.

This error could be confusing for those switching from excel. I think it would be helpful if OOo was to attempt to automatically apply the value() function to text, although it is more strictly correct to display the error.

I'll file an upstream bug on the main OOo site, if something similar hasn't already been reported.

Revision history for this message
Chris Cheney (ccheney) wrote :

mikedev,

Your problem is a separate issue apparently and is likely something that upstream would want to fix since it differs with how Excel works. The original bug in this report was about an issue that was caused by becoming more like Excel in what OOo accepts.

Changed in openoffice:
status: Invalid → New
Revision history for this message
stara (alex-kozlov) wrote : Re: [Upstream] [hardy] calc: sum show err:529

Change "." like 200.6 on "," 200,6 it will work

Revision history for this message
Noam Meltzer (tsnoam) wrote :

Hi,

I also have this problem, and it is f***ing annoying that they broke OOo behavior.
Anyhow, if we want to be practical, it is possible to use N(<cell>) to convert an empty cell/space/asterisk to zero.

Just my two cents...

- Noam

Revision history for this message
NoOp (glgxg) wrote :

For reference, here is the upstream bug:
http://www.openoffice.org/issues/show_bug.cgi?id=5658
Note that it has been ongoing since 2002.

Chris Cheney (ccheney)
Changed in openoffice:
importance: Undecided → Unknown
status: New → Unknown
Changed in openoffice:
status: Unknown → Confirmed
Revision history for this message
kajhaul (kaj-haulrich) wrote :

I solved my problem with Err. 529:

It seems that it was the formatting. After I marked all colums and choose format cells (all) and adjusting numbers, alignment and fonts it suddenly worked and the error disappeared.

Revision history for this message
Nicole (hasenhuette-linux) wrote :

Hallo!

I have solved the problem with installing the original version from www.openoffice.org

Revision history for this message
Arrigo Marchiori (ardovm) wrote :

Hello,

I am digging up this bug, please let me know if it's better to open a new one.

In reply to Chris Cheney, 2008-04-12: it seems to me that current versions of OpenOffice do _not_ show error 529 (or "#VALUE") but treat strings as zeroes instead.

In particular:
 - OpenOffice 3.0.1 for Windows, downloaded in binary form from openoffice.org web site;
 - OpenOffice 3.0.1 compiled from FreeBSD ports collection;
 - the version that Nicole (2008-09-28) said to have downloaded
all show the "old" behavior.

Since OO 3.0.1 is the latest version, I think that this contradicts what Chris was told at GoOOCon: official versions of OpenOffice still do treat strings as zeroes.

It seems to me that only the Ubuntu builds show an explicit error in this case. I've checked:
 - OpenOffice 2.4.1 included in Hardy;
 - latest version of OpenOffice for Hardy downloaded from ppa.launchpad.net (openoffice-pkgs).

I therefore suggest to let the Ubuntu build behave the same as the official one. I think that not doing so would break, for some users, one of the best features of OpenOffice: being a platform-invariant program.

Revision history for this message
Chris Cheney (ccheney) wrote : Re: [upstream] calc: sum show #VALUE or err:529

Arrigo,

Sort of, they are still trying to nail down what exactly to do in all cases so the change hasn't landed in official openoffice.org yet. It's currently targeted at '3.x' which means it should make into the OOo 3.x series once they do determine what to do.

Changed in openoffice.org:
importance: Critical → Medium
status: Invalid → Triaged
Revision history for this message
Chris Cheney (ccheney) wrote :

Arrigo,

Oh and this is only one of many places that official OOo differs from Go-OO builds. There are now over 800 patches in Go-OO.

Revision history for this message
Arrigo Marchiori (ardovm) wrote :

Chris,

thank you for the quick answer!

I hope this problem will be solved soon! In the meantime, I'll just avoid to do any formulas with strings and numbers together. :-)

Revision history for this message
Jon Thysell (jonthysell) wrote :

Just change all your functions that would have taken strings to be CONCATENATE(stringcell;"0"). ;)

+1 on reverting this back

Changed in openoffice:
status: Confirmed → In Progress
Changed in openoffice:
status: In Progress → Fix Released
Chris Cheney (ccheney)
summary: - [upstream] calc: sum show #VALUE or err:529
+ [upstream] [3.2] calc: sum show #VALUE or err:529
Revision history for this message
Christopher M. Peñalver (penalvch) wrote : Re: [upstream] [3.2] calc: sum show #VALUE or err:529

Christoph Roeder, this phenomenon is the same in LibreOffice Calc, Gnumeric, and Excel. The reason being is Calc and Gnumeric are designed to hold compatibility with Excel. Microsoft disclaims this issue noting "...add, divide, multiply, and subtract two or more numeric values.":

http://office.microsoft.com/en-us/excel-help/use-excel-as-your-calculator-HA010263680.aspx?CTT=5&origin=HA010215738

A cell with a "" is not a numeric value.

Despite this, this issue really is not a bug and requires more discussion, which should be done on an appropriate mailing list or forum. If you have a Microsoft support contract you can leverage, feel free to bring this to the attention of Microsoft and post the results to this bug. Otherwise, feel free to post to one or both of the following Microsoft forums and post a link of it to this bug:

http://social.msdn.microsoft.com/Forums/en-US/exceldev/threads
http://answers.microsoft.com/en-us/office/forum/excel?page=1

Changed in libreoffice (Ubuntu):
status: New → Opinion
importance: Undecided → Wishlist
Changed in openoffice.org (Ubuntu):
importance: Medium → Wishlist
status: Triaged → Opinion
Revision history for this message
Christopher M. Peñalver (penalvch) wrote :

Unlinking upstream bug as it has nothing to do with this issue.

Changed in openoffice:
importance: Unknown → Undecided
status: Fix Released → New
status: New → Opinion
summary: - [upstream] [3.2] calc: sum show #VALUE or err:529
+ Calc =""+1 shows error #VALUE or err:529
Changed in openoffice.org (Ubuntu):
status: Opinion → Won't Fix
Revision history for this message
Björn Michaelsen (bjoern-michaelsen) wrote : migrating packaging from OpenOffice.org to Libreoffice

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

Revision history for this message
Christopher M. Peñalver (penalvch) wrote :

No relevant reference URL.

Changed in openoffice:
status: Opinion → Invalid
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.