View previous topic :: View next topic 
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(E2F2) 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.791.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 (11=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 


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(E2F2) 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 


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 7hour editing spree!?? 

Back to top 


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 7hour 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 


JohnV Administrator
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. 

Back to top 


David Super User
Joined: 24 Oct 2003 Posts: 5668 Location: Canada

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. 

Back to top 


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 

Back to top 


