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?

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" <[email protected]> wrote in message
news:[email protected]...
>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?

In article <#[email protected]>, "Chip Pearson" <[email protected]> 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)

"Bruce Sinclair"
<[email protected]> 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"
<[email protected]> wrote in
message news:[email protected]...
> In article <#[email protected]>, "Chip
> Pearson" <[email protected]> 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)
>

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.