Column sum incorrect upon subtracting

[Drew]

I have a column with numbers that is totaled at the bottom. The Auto Sum
formula correctly adds all the cells. I tried to make two of the cells
negative so that they would be subtracted. But, the cell amount is being
doubled before it is subtracted from the total. So, if the cell amount is 50,
for some reason 100 is being subtracted. I have all cells formatted as
numbers, not general or text, and have checked for hidden formulas (there are
none). Here is my example to show what is happening:
=SUM(B2:B6)
where
B2 = 5
B3 = 5
B4 = 10
B5 = 15
B6 = 15

My total is correct at 50. But if I try to make B3 -5 and B4 -10, it is
doubling each cell amount before it's subtracting, so it's subtracting 30
instead of 15. PLEASE HELP!! Thank you!!

 

[Sean Timmons]

Just making sure here... changing those cels means you end up with 20. What
result are you getting?

 

[JLatham]

Your result of 20 is correct.

The "delta" or amount of change from a +5 to -5 is 10, and delta from +10 to
-10 is 20, so that's a total delta of 30, and 50-30 = 20.

The OP is thinking that the total should only be reduced by -5 + -10, or 15
(I think) and is expecting a result of 35.

To get that result, B3 and B4 need to both be zero, for a delta of -15.

 

[Peo Sjoblom]

5+5+10+15+15 = 50

5-5-10+15+15 = 20


So what you have is


5+15+15 =35

then

-5-10=-15


35-15=20







--


Regards,


Peo Sjoblom

 

[Joe User]

My total is correct at 50. But if I try to make B3 -5 and B4 -10, it is
doubling each cell amount before it's subtracting, so it's subtracting
30 instead of 15.

"Subtracting 30 instead of 15" from what?

I suspect you are saying that when you change B3 to -5 and B4 to -10, the
sum becomes 20 -- which is correct, by the way.

I think you are misinterpreting that as 50 - 30, and you are incorrectly
expecting 50 - 15, i.e. 50 - 5 - 10.

With the change, the sum is 5 + (-5) + (-10) + 15 + 15, which can be written
5 - 5 - 10 + 15 + 15. Noting that the middle -15 + 15 is zero, the
remaining sum is 5 + 15, which is 20.


----- original message -----

 

[Bill Sharpe]

I have a column with numbers that is totaled at the bottom. The Auto Sum
formula correctly adds all the cells. I tried to make two of the cells
negative so that they would be subtracted. But, the cell amount is being
doubled before it is subtracted from the total. So, if the cell amount is 50,
for some reason 100 is being subtracted. I have all cells formatted as
numbers, not general or text, and have checked for hidden formulas (there are
none). Here is my example to show what is happening:
=SUM(B2:B6)
where
B2 = 5
B3 = 5
B4 = 10
B5 = 15
B6 = 15

My total is correct at 50. But if I try to make B3 -5 and B4 -10, it is
doubling each cell amount before it's subtracting, so it's subtracting 30
instead of 15. PLEASE HELP!! Thank you!!

I can see Drew banging his hand against his head and saying "Oh, Right!"

Bill

 

[Gord Dibben]

Unless the surname is Barrymore in which case she would be flashing
Letterman


Gord