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

Adding and subtracting negative and positive numbers.

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
Jim Brownpants
Newbie
Newbie


Joined: 14 Nov 2009
Posts: 3

PostPosted: Sat Nov 14, 2009 2:32 am    Post subject: Adding and subtracting negative and positive numbers. Reply with quote

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


Joined: 08 Sep 2005
Posts: 83
Location: Finland

PostPosted: Sat Nov 14, 2009 3:40 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Jim Brownpants
Newbie
Newbie


Joined: 14 Nov 2009
Posts: 3

PostPosted: Sat Nov 14, 2009 5:59 am    Post subject: Reply with quote

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


Joined: 08 Sep 2005
Posts: 83
Location: Finland

PostPosted: Sat Nov 14, 2009 7:08 am    Post subject: Reply with quote

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))
Back to top
View user's profile Send private message
JohnV
Administrator
Administrator


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

PostPosted: Sat Nov 14, 2009 7:32 am    Post subject: Reply with quote

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


Joined: 24 Oct 2003
Posts: 5668
Location: Canada

PostPosted: Sat Nov 14, 2009 8:45 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Jim Brownpants
Newbie
Newbie


Joined: 14 Nov 2009
Posts: 3

PostPosted: Sun Nov 15, 2009 3:41 am    Post subject: thanks Reply with quote

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
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
Page 1 of 1

 
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