Column sum incorrect upon subtracting

  • Thread starter Thread starter Drew
  • Start date Start date
D

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!!
 
Just making sure here... changing those cels means you end up with 20. What
result are you getting?
 
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.
 
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
 
Drew said:
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.
Click to expand...

"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 -----
 
Drew said:
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!!
Click to expand...

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

Bill
 
Back
Top