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

Bug #210153 reported by Christoph Roeder on 2008-04-01
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

Christoph Roeder (brightdroid) wrote :
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?

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) on 2008-04-02
Changed in openoffice.org:
importance: High → Critical

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

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

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.

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.

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

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

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

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) on 2008-05-08
Changed in openoffice:
importance: Undecided → Unknown
status: New → Unknown
Changed in openoffice:
status: Unknown → Confirmed
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.

Nicole (hasenhuette-linux) wrote :

Hallo!

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

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.

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

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

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) on 2009-09-11
summary: - [upstream] calc: sum show #VALUE or err:529
+ [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

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

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

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.