| View previous topic :: View next topic |
| Author |
Message |
fdservices
General User
Joined: 12 Nov 2004
Posts: 37
|
 Posted: Mon Jun 30, 2008 11:52 pm Post subject: |
 |
|
With the right diagnosis your spreadsheet will work fine. There is nothing wrong with your logic.
If you want to send it to me I will find out why you have a problem with those two cells.
But, if you want it to work with all the various flavours of OpenOffice and Excel, use the SUM() formula above instead of the straight addition. This should avoid the error.
All the best
Andrew |
|
| Back to top |
|
 |
rokky
General User
Joined: 29 Jun 2008
Posts: 5
Location: NC
|
| Posted: Tue Jul 01, 2008 2:52 pm Post subject: Got a Sample |
|
|
Thanks for the offer, but no need to send my full-fledged financial info  . I was able to copy one of the problem rows and the one above it to a new sheet, changed "incriminatiing" info, and the behavior still occurs. And when I plugged in your formula using SUM, it gave correct results.
I am thinking if I were to use that new formula, I would have to apply it to 1000's of rows scattered over dozens of files for the last 7-8 years of check registers and derivative tax worksheets - is there some "batch" way to change a formula cell in that scenario?
TIA
Rokky |
|
| Back to top |
|
|
fdservices
General User
Joined: 12 Nov 2004
Posts: 37
|
| Posted: Tue Jul 01, 2008 10:47 pm Post subject: |
|
|
So perhaps you could post your "new" sheet with the dummy information?
I would suggest that you simply change the formulas when and if you need to, to save time.
Update: =+SUM(A1;C1) -SUM(B1) works better than =SUM(+A1;-B1;+C1)
Apparently the sign means that the typing of the cells still occurs, even in SUM() on some occasions.
All the best
Andrew |
|
| Back to top |
|
|
cobo
General User
Joined: 01 Apr 2008
Posts: 35
|
| Posted: Thu Jul 03, 2008 7:56 am Post subject: |
|
|
Hi all,
the behaviour reported by fdservices seems to be a linux-only issue. I tested the formula =a1+b1+c1 with A1 being a string, B1 and C1 being integers (1) with OpenOffice 2.4.0 on Windows XP (or Windows 2000): the result is 2. No error.
Tested on OpenOffice 2.4.0 on ubuntu: the result is Err: 529.
Another link on this issue:
https://bugs.launchpad.net/openoffice/+bug/210153
Cheers,
cobo |
|
| Back to top |
|
|
fdservices
General User
Joined: 12 Nov 2004
Posts: 37
|
| Posted: Thu Jul 03, 2008 8:04 am Post subject: |
|
|
Nope, it is a build issue.
The vanilla build of 2.4.1 does not exhibit the problem, but the Mandriva rpm does. I think that the goo builds do as well. Also Excel has it. of course. Caveat emptor!
All the best
Andrew |
|
| Back to top |
|
|
rokky
General User
Joined: 29 Jun 2008
Posts: 5
Location: NC
|
| Posted: Sun Jul 13, 2008 9:22 am Post subject: The culprit is a space character |
|
|
I finally "caught it in the act" of going from ok to err529 - it occurs when I type a space (accidental/habit) in the credit/deposit column. It goes to err529 as soon as I hit enter or cursor-move to another cell. Interestingly, as soon as I backspace over that space, the err529 goes away - don't even need to move to another cell or hit enter.
Furthermore, the problem does not occur in the debit column due to the ABS function applied to it. If I apply that function to the credit cell, spacing does not trigger err529:
=F533-ABS(D534)+ABS(E534) instead of my usual =F533-ABS(D534)+E534
And, of course, it does not occur with the Windows version, so does that mean the Linux version is more "faithful" than the Windows version OO in reproducing the latest(?) Excel "behavior"? Interesting...
rokky |
|
| Back to top |
|
|
fdservices
General User
Joined: 12 Nov 2004
Posts: 37
|
| Posted: Sun Jul 13, 2008 12:17 pm Post subject: |
|
|
It is often interesting to see how many people do not actually read the threads properly. On 30 June I posted "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) "!
Clearly the ABS() function works in the same way as the SUM() function i.e. prevents the "typing" of the cell so that the formula evaluates (IMV) correctly.
Also see the previous post about build issues.
All the best
Andrew |
|
| Back to top |
|
|
rokky
General User
Joined: 29 Jun 2008
Posts: 5
Location: NC
|
| Posted: Sun Jul 13, 2008 2:25 pm Post subject: |
|
|
| fdservices wrote: | It is often interesting to see how many people do not actually read the threads properly. On 30 June I posted "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) "!
Clearly the ABS() function works in the same way as the SUM() function i.e. prevents the "typing" of the cell so that the formula evaluates (IMV) correctly.
Also see the previous post about build issues.
All the best
Andrew |
I read that suggestion, and thought I had checked for spaces - guess not (or not correctly .
I am using Ubuntu 8.04, so am sort of stuck with whatever they pass along from their repositories (not willing to fool with installing "from scratch" - have had enough trouble with Seamonkey doing that). At least the Windoze version installation is more under my control, and works ok (what am I saying ???? Windoze and more control???...)
rokky |
|
| Back to top |
|
|
jonadab
Newbie
Joined: 21 Oct 2008
Posts: 1
Location: Ohio
|
| Posted: Tue Oct 21, 2008 5:58 am Post subject: |
|
|
| fdservices wrote: | | I would suggest that you simply change the formulas when and if you need to, to save time. |
So I should just forget about being able to refer back to all of my financial records from the past eleven years, not to mention all of the other spreadsheets I've created for various things, especially at work... annual reports, ...
I should forget all that stuff, and only change the formulas in my current spreadsheets and be happy?
Why anyone ever need to open existing documents and have them work as they did before?
What is everyone with existing spreadsheets supposed to do, maintain an old computer with OOo 2.x for the rest of our natural lives? |
|
| Back to top |
|
|
pingvin234
Newbie
Joined: 02 Nov 2009
Posts: 1
|
| Posted: Mon Nov 02, 2009 12:20 pm Post subject: |
|
|
| I had the same problem with error 529, i am using linux, SUSE distribution. The solution is quite simple, the cells that are being calculated have a wrong character for decimal separation. Instead of comma "," probably a point ".". Redo them all and ONLY use numeric part of the keyboard, not alphanumeric part of the keyboard. This worked for me. |
|
| Back to top |
|
|
cwli
Newbie
Joined: 18 Nov 2011
Posts: 4
|
| Posted: Fri Nov 18, 2011 11:39 am Post subject: |
|
|
I'm using Openoffice in Linux.
I've found an answer as I had the same problem. I had a spreadsheet where C2 would be
=C1+B2-A2
and C1 would have a number, say, 1000.
C3, C4, C5 etc. would have the corresponding formula. Then somewhere at, say, C150, I would get ERR:529; A150 would have a number inside it but B150 did not. I found that by inserting a zero in B150 the error went away.
This problem did not occur in Openoffice 2. |
|
| Back to top |
|
|
|
