How do I sum the absolute values of a range of cells?
 

I want to obtain the sum of a group of cell using the absolute values of what
is contained in these cells. How can I do this without creating a seperate
cell for each to reduce with the ABS function?

How do I sum the absolute values of a range of cells?
 

Use the following array formula:

=SUM(ABS(A1:A10))

Since this is an array formula, you must press CTRL+SHIFT+ENTER
rather than just ENTER when you first enter the formula and
whenever you edit it later. If you do this properly, Excel will
display the formula enclosed in curly braces {}.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"fsinsd" wrote in message
...
I want to obtain the sum of a group of cell using the absolute
values of what
is contained in these cells. How can I do this without
creating a seperate
cell for each to reduce with the ABS function?

How do I sum the absolute values of a range of cells?
 

"Bruce Sinclair"
wrote in
message
it would seem to be
because there is a range involved in the calculation instead of
a cell.

Yes, the formula needs to be array-entered because the entire
range A1:A10 needs to be passed to ABS, and ABS returns an array
of values, each of which is the absolute value of input element.

See http://www.cpearson.com/excel/array.htm for more info about
array formulas.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Bruce Sinclair"
wrote in
message ...
In article , "Chip
Pearson" wrote:
Use the following array formula:

=SUM(ABS(A1:A10))

Since this is an array formula, you must press CTRL+SHIFT+ENTER
rather than just ENTER when you first enter the formula and
whenever you edit it later. If you do this properly, Excel will
display the formula enclosed in curly braces {}.

Hi Chip

Can you clear something up for me ? ... I've never really
understood why
some formulae are "array" and some are not. Looking at your
example above
(one of the shortest array formula I've seen :) ) it would seem
to be
because there is a range involved in the calculation instead of
a cell.

Am I onto it here ... or still missing something ? :)

IIRC there are some functions that are array formulae by their
nature ...
but that nature may again simply be that underlying range of
values ...
which may not be immediately obvious to some of us :).

Thanks


Bruce

----------------------------------------
I believe you find life such a problem because you think there
are the good
people and the bad people. You're wrong, of course. There are,
always and
only, the bad people, but some of them are on opposite sides.

Lord Vetinari in Guards ! Guards ! - Terry Pratchett

Caution ===== followups may have been changed to relevant
groups
(if there were any)

How do I sum the absolute values of a range of cells?
 

In article , "Chip Pearson" wrote:
Use the following array formula:

=SUM(ABS(A1:A10))

Since this is an array formula, you must press CTRL+SHIFT+ENTER
rather than just ENTER when you first enter the formula and
whenever you edit it later. If you do this properly, Excel will
display the formula enclosed in curly braces {}.

Hi Chip

Can you clear something up for me ? ... I've never really understood why
some formulae are "array" and some are not. Looking at your example above
(one of the shortest array formula I've seen :) ) it would seem to be
because there is a range involved in the calculation instead of a cell.

Am I onto it here ... or still missing something ? :)

IIRC there are some functions that are array formulae by their nature ...
but that nature may again simply be that underlying range of values ...
which may not be immediately obvious to some of us :).

Thanks


Bruce

----------------------------------------
I believe you find life such a problem because you think there are the good
people and the bad people. You're wrong, of course. There are, always and
only, the bad people, but some of them are on opposite sides.

Lord Vetinari in Guards ! Guards ! - Terry Pratchett

Caution ===== followups may have been changed to relevant groups
(if there were any)

How do I sum the absolute values of a range of cells?
 

Chip Pearson wrote:
Use the following array formula:

=SUM(ABS(A1:A10))

Since this is an array formula, you must press CTRL+SHIFT+ENTER
rather than just ENTER when you first enter the formula and
whenever you edit it later. If you do this properly, Excel will
display the formula enclosed in curly braces {}.

Or

=SUMPRODUCT(ABS(A1:A10))

which does not need to be entered as an array formula.