ABS

[Guest]

How do I change the cells in a column to absolute values then add them up in
one formula or function?

 

[Guest]

Maybe:-

=SUM(ABS(A1:A20))

enter with Ctrl+Shift+enter

Mike

 

[Dave Peterson]

You can't change the value in a cell using a formula.

But you can sum the absolute values of a range:
=SUMPRODUCT(ABS(A1:A10))
Adjust the range to match, but you can't use the whole column until xl2007.

 

[Guest]

Maybe:-

=SUM(ABS(A1:A20))

enter with Ctrl+Shift+enter

Mike

 

[James Silverton]

You can't change the value in a cell using a formula.

But you can sum the absolute values of a range:
=SUMPRODUCT(ABS(A1:A10))
Adjust the range to match, but you can't use the whole column
until xl2007.

I am somewhat puzzled! If I have 2000 random numbers both
positive and negative in column A, I can sum them with =SUM(A:A)
in say B2000.
=SUM(ABS(A:A)) in C2000 also works.

I am using Excel2000.

 

[James Silverton]

James wrote on Thu, 18 Oct 2007 15:49:31 -0400:

JS> message ??>> You can't change the value in a cell using a formula.
??>>
??>> But you can sum the absolute values of a range:
??>> =SUMPRODUCT(ABS(A1:A10))
??>> Adjust the range to match, but you can't use the whole
??>> column until xl2007.
??>>
??>> pcj101 wrote:
??>>>
??>>> How do I change the cells in a column to absolute values
??>>> then add them up in one formula or function?
??>>
??>> --
??>>
??>> Dave Peterson

JS> I am somewhat puzzled! If I have 2000 random numbers both
JS> positive and negative in column A, I can sum them with
JS> =SUM(A:A) in say B2000.
JS> =SUM(ABS(A:A)) in C2000 also works.

JS> I am using Excel2000.

I think I begin to see what is happening (lights flashing etc.)
The program will accept =SUM(ABS(A:A)) but the value
produced is incorrect., unlike =SUM(A:A). I'd need a helper
column =ABS(a1) etc. to get the correct answer.



James Silverton
Potomac, Maryland

E-mail, with obvious alterations:
not.jim.silverton.at.verizon.not

 

[Dave Peterson]

You can use:
=sum(abs(a1:a10))
but only if you remember to use ctrl-shift-enter

Or you can use =sumproduct(abs(a1:a10)) with out array entering the formula.

 

[RagDyer]

Probably easier then a helper column is to simply remove one row:

=SUM(ABS(A1:A65535))

Array entered.

Unless, of course, if you're in XL07.

 

[James Silverton]

Probably easier then a helper column is to simply remove one
row:

=SUM(ABS(A1:A65535))

Array entered.

Unless, of course, if you're in XL07.

Interesting!

Thanks!