subtraction of negative numbers

[Roger]

I'm trying to create an equation that, on occation, will result in a
subtraction of a negative, which then results in a larger negative number
than it should be.

So for example:

=sum(A1 - B1)
A1 = -50
B1 = 40

so the results of this equation is -90, but in reality it should be -10.

seems simple but it's confusing the hell out of me.

thanks in advance for the help.
roger

 

[Richard Schollar]

Hello Roger

The formula should be:

=SUM(A1:B1)

What you were effectively doing was

=A1-B1

Richard

 

[John C]

Anytime you subtract a negative, you make it a positive. That is not your
error here though.

You have A1(-50) MINUS B1(40), ... -50-40=-90
The result you are requsting is A1+B1, ... -50+40=-10

Hope this helps.

 

[Pete_UK]

Well, if you take 40 away from -50 the answer is -90. Imagine you were
overdrawn at your bank by $50 and you wrote a check for $40 - you
would then be overdrawn by $90. (I'm assuming you are in the US)

If you just want the numerical difference between the two numbers, and
for this to be expressed as a positive number, you can do this:

=ABS(ABS(A1)-ABS(B1))

Hope this helps.

Pete

 

[T. Valko]

So, if *either* number is negative then the result should be negative?

Try this array formula** :

=IF(COUNTIF(A1:B1,"<0"),-SUM(ABS(A1:B1)),A1+B1)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Or, normally enter this slightly longer version:

=IF(COUNTIF(A1:B1,"<0"),-SUM(ABS(A1),ABS(B1)),A1+B1)

 

[Roger]

I realize I didn't complete my problem, that is that column A will have both
negative and positive numbers, so A1+B1 will be incorrect in those cases.

I want -50 - +40 = -10

Hope that makes sense.

 

[John C]

-50 - +40 is still = -90
As the others posted, you really want
-50+40 = -10
Now, if the 40 is being displayed as a positive number, but it has a
negative reference (i.e.: even though it shows as 40 in cell B1, and not -40
in B1), then you want A1+B1 which is the same as -50 + 40 = -10 which is
the same as
-50 - -40 = -10

 

[bwbmom]

I have a similar problem but I don't want the result to always be negative.
If A1 is negative and B1 is positive then I want the negative in A1 to be
reduced by the positive in B1 and reflect a negative amount (ie. -900 minus
+200 = -700) but sometimes the amount in A1 is positive and B1 is positive
and so then I just want B1 subtracted from A1 to reflect a positive (i.e.
+900 minus +200 = +700).

 

[Glenn]

=A1-B1*SIGN(A1)

I have a similar problem but I don't want the result to always be negative.
If A1 is negative and B1 is positive then I want the negative in A1 to be
reduced by the positive in B1 and reflect a negative amount (ie. -900 minus
+200 = -700) but sometimes the amount in A1 is positive and B1 is positive
and so then I just want B1 subtracted from A1 to reflect a positive (i.e.
+900 minus +200 = +700).

 

[bwbmom]

Thanks Glenn - I used the example you gave in the other thread and it worked
perfectly.