[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
Jim Brownpants
Newbie

Joined: 14 Nov 2009
Posts: 3

 Posted: Sat Nov 14, 2009 2:32 am    Post subject: Adding and subtracting negative and positive numbers. Hi, I am new to this site but came here because of one specific problem: calc's inability to add and subtract two columns of numbers correctly if negative numbers are involved. (Or perhaps it is my inability to use calc, but that is another story!) Let's say I have 2 rows of numbers. In E2, there is 0.79. In F2, there is -1.26. Now, either =sum(E2+F2) gives -1.26 =sum(E2-F2) gives +1.26 =sum(E2:F2) gives -1.26 These results are however wrong. funnily enough, calc can do the calculation fine if i enter the numbers manually, i.e =sum(0.79--1.26)=2.05 So why can't it do the calculation using the references E2 or F2 or whatever. I have about 3000 rows of data, some positive and some negative. Is there any way to do this!? Thanks in advance, Dave. And please, before posting, let's not get involved in a maths debate here. Two positive signs together (+1++1=2) and two negative signs (1--1=2) are both the same as a plus....one positive sign and one negative sign next to each other (+1-+1=0) or (+1+-1=0) equals a minus.
gpl_racer
Power User

Joined: 08 Sep 2005
Posts: 83
Location: Finland

 Posted: Sat Nov 14, 2009 3:40 am    Post subject: For me the formulas work correctly: =SUM(E2-F2) returns 2,05 =SUM(E2:F2) returns -0,47 Only way I was able to get the same results as you was by using the wrong delimiter for the 0,79 (i.e. using a dot instead of a comma in my case) or by inserting the value 0,79 with a macro using setString instead of setValue. In both cases calc handled 0,79 as a string instead of a number, which caused the wrong results.
Jim Brownpants
Newbie

Joined: 14 Nov 2009
Posts: 3

 Posted: Sat Nov 14, 2009 5:59 am    Post subject: okay! when i type the numbers in manually it works correctly, so i guess i will have to do that for all of my 3000+ rows the current numbers are the result of a SUBSTITUTE formula and for some reason calc doesn't seem to be able to handle them any way around it before i embark on a 7-hour editing spree!??
gpl_racer
Power User

Joined: 08 Sep 2005
Posts: 83
Location: Finland

Posted: Sat Nov 14, 2009 7:08 am    Post subject:

 Jim Brownpants wrote: the current numbers are the result of a SUBSTITUTE formula and for some reason calc doesn't seem to be able to handle them any way around it before i embark on a 7-hour editing spree!??

That means calc is handling them as text strings instead of numbers. Try using VALUE() around the cell addresses, for example:
 Code: =SUM(VALUE(E2)-VALUE(F2))
JohnV

Joined: 07 Mar 2003
Posts: 9183
Location: Lexinton, Kentucky, USA

 Posted: Sat Nov 14, 2009 7:32 am    Post subject: To convert text numbers (left aligned) to numbers use Find and Replace. Search = ^. Replace = & Under More Options check Regular Expressions Click Replace All It works, just don't ask why.
David
Super User

Joined: 24 Oct 2003
Posts: 5668

 Posted: Sat Nov 14, 2009 8:45 am    Post subject: Also, I question the need to use the SUM() function at all when there are just two cells involved. With 0.79 in E2 and -1.26 in F2, then =E2+F2 would give the correct result, -0.47. As pointed out, probably one or the other is text instead of number. David.
Jim Brownpants
Newbie

Joined: 14 Nov 2009
Posts: 3

 Posted: Sun Nov 15, 2009 3:41 am    Post subject: thanks that info is all great for as first time user this kind of information is valuable thanks for all the support guys Regards, Jim Brownpants
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
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