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

F

fsinsd

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?
 
C

Chip Pearson

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
 
C

Chip Pearson

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

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"
 
B

Bruce Sinclair

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 {}.
Click to expand...

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)
 
P

Paul Lautman

Chip said:
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 {}.
Click to expand...
Or

=SUMPRODUCT(ABS(A1:A10))

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