Error 529

[fdservices]

This appears to be a new feature in OO, and seems to indicate a disturbing trend in the development.

Excel has, IMHO, a serious problem in dealing with simple arithmetic.

If you add two cells together, one if which contains text, then the result is a #VALUE error. However, if the text is a numeric representation, then Excel will perform the addition i.e The number 1 plus the text "1" will result in a value 2.

However, if you SUM() two cells, one of which contains text, the text cell will evaluate to zero and there will be no #VALUE error. If the text is a numeric representation then EXcel will not perform the addition and the result will be unchanged. i.e The number 1 summed with the text "1" will result in a value 1.

Fortunately there is a workaround in Excel, Tools > Options > Transition > Transition Evaluation. Using Transition Evaluation, Excel assign a value of 0 (zero) to a text cell and all the above evaluate correctly.

It now seems that OO have adopted the same inconsistent arithmetic in Calc in 2.4, in order to "be compatible with Excel". They have not, however, added the "Transition Evaluation" fix. So now the majority of my Excel Spreadsheets cannot be read by OO and will not write back correctly to Excel!

Big improvement OpenOffice. Who wants to be compatible anyway? Why can't we have a spreadsheet that is better than Excel?

[AndrewZ]

Are you comparing 2.4 to 2.3.1? If you wish to file a bug report, send it to the Issue Tracker at http://qa.openoffice.org after verifying it is not already been filed. If you think it is a serious, unintentional regression, see the instructions at http://wiki.services.openoffice.org/wiki/OOoRelease241
_________________
<signature>
* Did you solve your problem? Do others a favor: Post the solution
* OpenOffice.org Ninja
* BleachBit
</signature>

[Mark B]

[diederic]

Hi List,
In 'Help' can't find Err:529? What is it anyway?
I've got a whole collumn of them, starting at row 11; the same formula

=IF(AND($D11="x";OR($E11>0;$F11>0));$G10+$E11-$F11;$G10),

works well in the rows above.

Any suggestions?

[fdservices]

Hi AndrewZ, as I said, this does not appear to be a bug from the conversations I have seen with the developers, but a newly introduced "feature".

MarkB, it does not work like that in my versions, see below.

diederic, it is the text evaluating as an error problem. Try this:

In cell A1 enter the number one (1), in cell B1 enter the number two (2), in cell C1, enter the formula =A1+B1. The result will be three (3).

Now change the contents of A1 to A. The result shown in cell C1 will be Err:529
Also try changing the contents of A1 to a space character. Same result Err:529, but it actually looks as though it should be OK

Finally change the formula in C1 to SUM(A1:B1). Wow, it evaluates correctly now.

This is not consistent behaviour, is it?

[diederic]

Hi
referring to me post error529: If I enter zero =0 into the cells above the message 'error529', the column adds and error529 vanishes.

In help error 529 can't be found.

Cheers,
Diederic

[fdservices]

Diederic

That is a bit strange. If you enter that formula into a blank spreadsheet at G11 then it evaluates to zero correctly. If you copy the formula up from B2 to G10 then it still works OK.
If any cell in column D contains "x" and the cells in E or F on the same row contain text, then you get an Err:529. I think you must have had a text cell in a previous row.

I am not sure if the OR statement should also be an AND, presumably you are testing for positive numbers in $E11 and $F11, but in your formula if either $E11 or $F11 are positive then both cells will be used in the total, even if one is negative.

You can stop looking for help on Err:529, I don't think that it exists yet.

Have you tried Gnumeric? It seems to handle this a little better than OO.

All the best


Andrew

[rokky]

I hit this with my first use of OO Calc 2.4 on a new Ubuntu 8.04 upgrade, and it concerns me greatly about reliability of OSS upgrades since I use it for my electronic check register. I do make a copy in Excel 5.0 format as a fallback (since a problem a year or 2 ago with xml corruption in the spreadsheet file with OO 1.<something> - used up most of a Saturday finding a Windows xml editor to clean that up, and use it ... ugh!).

What I found triggering it was just a few empty cells in the "deposit" column (have very few deposit entries vs debit/check entries for some reason - maybe 3 or 4 rows with this problem out of over 400. If I put a zero in the cell, it then corrects the "balance" cell (where the error appeared - formula: "=F221-ABS(D222)+E222" - "F" is balance column, "D" is debit column, "E" is credit/deposit column). I thought at first it was an issue with the deposit cell being currency vs numeric format - not all were set the same depending on how I added the row for that entry - but switching those back and forth does not seem to matter - it just has to have a zero in those few deposit cells that throw off the balance formula.

I have not tried the Windows version yet, but will when I reboot the machine back to the Win2K system partition - just installed latest OO 2.4 there yesterday.

This kind of regression is VERY disturbing when it hits my faltering attempts to keep track of money - a very irksome chore for me already ...

rokky

[David]

[fdservices]

Hi David, I would like to say that although Linux and OpenOffice are free, that is no reason why they should not aspire to be better than other operating systems and office software. I hope that we can continue to question developments, and assist in our small way in improving the software by pointing out these inconsistencies.

Rokky, I think you have hit the same problem as is described above. Excel started this strange behaviour and now OpenOffice (some versions apparently) has mimicked it in the mistaken (I believe) idea that its should do exactly what Excel does instead of improve on it. I am sure that if you examine the apparently blank cells in your sheet you will find either a stray character (normally a "space" character) of an inserted blank ("") from a formula. Either will produce your error. I cannot reproduce this as my OpenOffice version works correctly (in my opinion), but that is what happens in Excel.

I would suggest that you try the formula =SUM(F221;-ABS(D222);+E222) this should work correctly in both Excel and, as far as I know, all versions of OpenOffice.

Why do you use ABS() for the debit column?

All the best

Andrew

[David]

[fdservices]

Quite right, could not agree more.

Do you remember \X in Visicalc? You must be as old as I am...........

All the best

Andrew

[rokky]

1. Sorry for sounding so irked - I had been working several hours catching up on a month's worth of checking transactions, so was a bit ragged by the time I noticed this problem, and found this thread and several references in other fora to this particular problem. I felt blindsided by such a weird random problem that I had never seen before.

2. I have purchased Star Office 5.something for my OS/2 usage in the (distant) past, and Star Office 7 as sold by Sun at a CompUSA several years ago, so I have not always "mooched" for free. I have been using the free OO since about 1.2 (maybe 1.1?).

3. This is very sporadic - only the 2 credit cells in 2 rows out of 537 had the problem - all others are quite ok with no entry vs the zero they needed. I have not found any difference in values and formats that explain the differing behavior (some are currency format, some are "plain" number format - I have not gone as far as copying other non-problem "empty" cells into the problem ones in the case of "hidden" null data - maybe when I do the next update - before another month goes by it is to be hoped .

4. The problem does not occur with the exact same ODS and XLS files (on a shared FAT 32 partition) in Wndows 2000 with the Windows OO 2.4 I just dl'ed and installed a few days ago.

5. I use ABS because at one point my bank was providing the electronic transactions for checks/debits in "classic" accounting style as negative numbers, but sometimes in rerranging/or otherwise cleaning them up for pasting into the sheet I made them positive, and other times, I did not need to do such mods, so copied them as they were. Thus, I found ABS a useful practice ("error trapping"?). I have not had to deal with that format for several years now since the bank was acquired by SunTrust, and they totally changed the electronic transactions formats to all positive numbers.

6. What kind of "error trapping" can one do for sporadic program errors? I have used this format for years with OO/StarOffice, and never had an issue with blanked cells being used by my formula, and even now 2 out of 500+ seems unsuitable to be characterized as "user error". Note that the 1st row that showed this error had been originally created/processed earlier this year with OO 2.1 or 2.2 with no problem, and it was only yesterday with my first use of 2.4 that it was flagged with the 529 error - this is NEW sporadically erroneous behavior versus prior versions of OO with the same data (and vs the same level of the Windows version).

7. All this effort I have put into this reporting so far was in the hope that it would corroborate and shed light on the earlier postings. I might find time and energy to switch back to Ubuntu to play with further pinpointing what seems to trigger this problem, and see if it will copy successfully into a sample I could upload for analysis, but don't know if/when I can get to that - would it really be worthwhile from a debugging perspective? Has no one else in development been able tor re-create the issue?

PAX,
rokky

[David]

[David]