abs() in EXCEL

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

George - 17 Jul 2006 10:16 GMT

Hi.
I would like to calculate (only in one cell) the sum of absolute values (
ABS() ) of a column. The column includes positive and negative numbers and
some blank cells. I would like to reject the non number cells.
Is this possible?
Th.
p.s.
Could a form of SUM ( SUMIF, SUMPRODUCT, e.g.) help?

Reply to this Message

Bondi - 17 Jul 2006 10:39 GMT

> Hi.
> I would like to calculate (only in one cell) the sum of absolute values (
[quoted text clipped - 4 lines]
> p.s.
> Could a form of SUM ( SUMIF, SUMPRODUCT, e.g.) help?

Hi George,

I guess one way to do it would be to use something like ( If your data
is in A1:A10):

=SUM(IF((A1:A10)<0,(A1:A10)*-1,A1:A10))

(sure there is a more elegant way)

It is an array formula so you have to activate it with Ctrl + Shift
Enter

Regards,
Bondi

Reply to this Message

Bob Phillips - 17 Jul 2006 11:32 GMT

=SUM(IF(ISNUMBER(A1:A20),ABS(A1:A20)))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

if it just blanks, not text, this will suffice

=SUM(ABS(A1:A20))

also an array formula

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

> Hi.
> I would like to calculate (only in one cell) the sum of absolute values (
[quoted text clipped - 4 lines]
> p.s.
> Could a form of SUM ( SUMIF, SUMPRODUCT, e.g.) help?

Reply to this Message

Rate this thread: