OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Error 529
Goto page 1, 2  Next
 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
fdservices
General User
General User


Joined: 12 Nov 2004
Posts: 37

PostPosted: Mon May 05, 2008 5:34 am    Post subject: Error 529 Reply with quote

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?
Back to top
View user's profile Send private message
AndrewZ
Moderator
Moderator


Joined: 21 Jun 2004
Posts: 4140
Location: Colorado, USA

PostPosted: Mon May 05, 2008 6:02 am    Post subject: Reply with quote

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>
Back to top
View user's profile Send private message Visit poster's website
Mark B
Super User
Super User


Joined: 16 Feb 2007
Posts: 852
Location: Lincolnshire, UK

PostPosted: Mon May 05, 2008 6:20 am    Post subject: Re: Error 529 Reply with quote

fdservices wrote:
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.


This appears to be the default in 3.0 - any text is given the value 0.


Note: Also tried this in 2.4 - same result (i.e. text is given the value 0).

Mark
_________________
Mark B's Articles
Back to top
View user's profile Send private message Send e-mail Visit poster's website MSN Messenger
diederic
Power User
Power User


Joined: 05 Jan 2005
Posts: 98

PostPosted: Mon May 05, 2008 2:24 pm    Post subject: Error529 Reply with quote

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?
Back to top
View user's profile Send private message
fdservices
General User
General User


Joined: 12 Nov 2004
Posts: 37

PostPosted: Mon May 05, 2008 11:20 pm    Post subject: Reply with quote

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 Crying or Very sad

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

This is not consistent behaviour, is it?
Back to top
View user's profile Send private message
diederic
Power User
Power User


Joined: 05 Jan 2005
Posts: 98

PostPosted: Sun May 25, 2008 12:48 am    Post subject: error529 Reply with quote

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
Back to top
View user's profile Send private message
fdservices
General User
General User


Joined: 12 Nov 2004
Posts: 37

PostPosted: Sun May 25, 2008 3:57 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
rokky
General User
General User


Joined: 29 Jun 2008
Posts: 5
Location: NC

PostPosted: Sun Jun 29, 2008 8:51 am    Post subject: More Data on ERR:529 Reply with quote

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 Wink - 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
Back to top
View user's profile Send private message
David
Super User
Super User


Joined: 24 Oct 2003
Posts: 5668
Location: Canada

PostPosted: Sun Jun 29, 2008 3:02 pm    Post subject: Reply with quote

Code:
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


You have a free operating system, and a free office suite. I wouldn't get too irked over that. You might be a bit more specific by uploading to a file hosting service a typical file [use false entries, of course] for us to test. You might also consider, when designing a spreadsheet, as one does when designing a program, doing some error trapping. There are several functions to allow that. We work with the tools we are given.

In future, could you all please start a new thread? It's difficult to consider two or three problems at the same time, even if related.

David.
Back to top
View user's profile Send private message
fdservices
General User
General User


Joined: 12 Nov 2004
Posts: 37

PostPosted: Mon Jun 30, 2008 4:23 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
David
Super User
Super User


Joined: 24 Oct 2003
Posts: 5668
Location: Canada

PostPosted: Mon Jun 30, 2008 7:15 am    Post subject: Reply with quote

fdservices wrote:
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.


I agree wholeheartedly. I just find it VERY disturbing [sic] when people do not so much make suggestions, but rather express poor opinion of this fine bit of work, and further expect it to be all things to all people. I'm constantly reminded of the adage that "No good deed goes unpunished." I have found it necessary to "work around "[aka "work with"] every spreadsheet or programming language I have ever encountered [**] since being self-taught with Visicalc, no text or help, just hacking. This is simply no different, and it is not possible to accommodate all contingencies.

[**] I had a great deal of effort, and fun, using several columns, ultimately hidden, when preparing a spreadsheet on fractions. The problem was to have the fractions in lowest form, and also in familiar [as in written] format. The fact that there is a "fractions" format for cells was quite immaterial, and not directly useful. That is what one must do... work with the tools available.

That is not to say that development should stop, but it should be development in the right direction, being general, and so useful to all, not necessarily specific to one need.

David.
Back to top
View user's profile Send private message
fdservices
General User
General User


Joined: 12 Nov 2004
Posts: 37

PostPosted: Mon Jun 30, 2008 8:03 am    Post subject: Reply with quote

Quite right, could not agree more.

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

All the best

Andrew
Back to top
View user's profile Send private message
rokky
General User
General User


Joined: 29 Jun 2008
Posts: 5
Location: NC

PostPosted: Mon Jun 30, 2008 6:25 pm    Post subject: Followup Reply with quote

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

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
Back to top
View user's profile Send private message
David
Super User
Super User


Joined: 24 Oct 2003
Posts: 5668
Location: Canada

PostPosted: Mon Jun 30, 2008 6:30 pm    Post subject: Reply with quote

fdservices wrote:
Quite right, could not agree more.

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

All the best

Andrew


NO. Likely older. The mind goes second. I can't remember what goes first.

David.
Back to top
View user's profile Send private message
David
Super User
Super User


Joined: 24 Oct 2003
Posts: 5668
Location: Canada

PostPosted: Mon Jun 30, 2008 6:36 pm    Post subject: Re: Followup Reply with quote

rokky wrote:
1. Sorry for sounding so irked


And I apologise if I sounded a bit high-horsed. I am just of the opinion that we all get frustrated, but that's the joy of programming. With some effort, you can make things work. If not, then it is simply not the right software for this particular job. In fact, there may be dedicated software specific to that need already available. So we have a choice: Buy a car, or build one from a pile of metal, rubber and plastic. The building can be fun ...or not.

David.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Goto page 1, 2  Next
Page 1 of 2

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group