sum negative numbers

[lv]

When I use SUM function to sum a column of positive and negative numbers - the sum is incorrect
If the column has values






SUM = 1

change 1 to -1 then SUM = 1
change 5 to -5 then SUM =

same thing happens if all values are negative and then you change one to positive

 

[Bernie Deitrick]

Actually, those sums are correct. When you change the 1 to a -1, you add up
the positive numbers 2+3+4+5 to get 14, then you subtract 1 (adding a
negative 1) to get 13.

If you want to sum only positive numbers (to get the 14), then use

=SUMIF(A1:A5,">0")

HTH,
Bernie
MS Excel MVP

When I use SUM function to sum a column of positive and negative numbers - the sum is incorrect.
If the column has values
1
2
3
4
5

SUM = 15

change 1 to -1 then SUM = 13
change 5 to -5 then SUM = 5

same thing happens if all values are negative and then you change one to

positive

 

[Ron Rosenfeld]

When I use SUM function to sum a column of positive and negative numbers - the sum is incorrect.
If the column has values
1
2
3
4
5

SUM = 15

change 1 to -1 then SUM = 13
change 5 to -5 then SUM = 5

same thing happens if all values are negative and then you change one to positive

What would you expect to happen?

In the math with which I am familiar, those are the correct answers.

1+2+3+4+5 = 15
-1+2+3+4+5 = 13
1+2+3+4+(-5) = 5


--ron

 

[Ken Wright]

I'd be extremely unhappy if it didn't come out with those answers

SUM = 15

change 1 to -1 then SUM = 13

From the 15, you have first lost the 1 that you started with, so decrement the
15 by 1 giving you 14, and then factor in the -1 which will take it down by a
further 1 to 13

change 5 to -5 then SUM = 5

From the 13, you have first lost the 5 that you started with, so decrement the
13 by 5 giving you 8, and then factor in the -5 which will take it down by a
further 5 to 3

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

When I use SUM function to sum a column of positive and negative numbers - the sum is incorrect.
If the column has values
1
2
3
4
5

SUM = 15

change 1 to -1 then SUM = 13
change 5 to -5 then SUM = 5

same thing happens if all values are negative and then you change one to

positive