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

[Christoph Roeder]

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

[lanzen (lanzen)]

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)]

Works in upstream openoffice.org 2.4.0

[Chris Cheney (ccheney)]

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

[lanzen (lanzen)]

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)]

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)]

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)]

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)]

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.

[stara (alex-kozlov)]

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

[Noam Meltzer (tsnoam)]

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)]

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.

[kajhaul (kaj-haulrich)]

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)]

Hallo!

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

[Arrigo Marchiori (ardovm)]

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.

[Chris Cheney (ccheney)]

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.

[Chris Cheney (ccheney)]

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)]

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)]

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

+1 on reverting this back

[penalvch (penalvch)]

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

[penalvch (penalvch)]

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

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

[penalvch (penalvch)]

No relevant reference URL.