Summing a Column - Net and Absolute Values

I've got a column of positive and negative numbers that I want to total in
two ways :

- net - ie just natural addition which I can do with the SUM formulae
- absolute - ie the total being based on an assumption that all numbers are
positive

A slightly messy way (because of the design of the spreadsheet) is to create
an extra column of absolute values and sum that.

So my question is 'Is there are formulae I can use to do an absolute sum on
those values directly?'

TIA

Rob

Pete_UK
Thu May 08 07:03:34 CDT 2008

Suppose the numbers are in column A, you can try this array* formula:

=3DSUM(ABS(A1:A10))

Adjust the range to suit.

* An array fromula must be committed using the key combination of CTRL-
SHIFT-ENTER (CSE) instead of the usual ENTER. If you do this correctly
then Excel will wrap curly braces { } around the formula when viewed
in the formula bar - you must not type these yourself. If you
subsequently amend/edit the formula you must use CSE again.

Hope this helps.

Pete

On May 8, 12:42=A0pm, "BRob" <del**eteto-hash#rfj1...@ntlworld.com>
wrote:
> I've got a column of positive and negative numbers that I want to total in=

> two ways :
>
> - net - ie just natural addition which I can do with the SUM formulae
> - absolute - ie the total being based on an assumption that all numbers ar=
e
> positive
>
> A slightly messy way (because of the design of the spreadsheet) is to crea=
te
> an extra column of absolute values and sum that.
>
> So my question is 'Is there are formulae I can use to do an absolute sum o=
n
> those values directly?'
>
> TIA
>
> Rob

JE
Thu May 08 07:07:14 CDT 2008

One way (Array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):

=SUM(ABS(A1:A10))


In article <uNBUj.91159$SY5.44650@newsfe13.ams2>,
"BRob" <del**eteto-hash#rfj1001@ntlworld.com> wrote:

> I've got a column of positive and negative numbers that I want to total in
> two ways :
>
> - net - ie just natural addition which I can do with the SUM formulae
> - absolute - ie the total being based on an assumption that all numbers are
> positive
>
> A slightly messy way (because of the design of the spreadsheet) is to create
> an extra column of absolute values and sum that.
>
> So my question is 'Is there are formulae I can use to do an absolute sum on
> those values directly?'
>
> TIA
>
> Rob

MikeH
Thu May 08 07:08:01 CDT 2008

For the absolute sum try

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

Which is an aeeay so commit with
Ctrl+Shift+Enter
The inner ISNUMBER isn't strictly necessary but takes care of any odd error
values.

Mike

"BRob" wrote:

> I've got a column of positive and negative numbers that I want to total in
> two ways :
>
> - net - ie just natural addition which I can do with the SUM formulae
> - absolute - ie the total being based on an assumption that all numbers are
> positive
>
> A slightly messy way (because of the design of the spreadsheet) is to create
> an extra column of absolute values and sum that.
>
> So my question is 'Is there are formulae I can use to do an absolute sum on
> those values directly?'
>
> TIA
>
> Rob
>
>
>
>
>
>
>

Ron
Thu May 08 07:06:50 CDT 2008

With your list of pos/neg values in A1:A10

This formula converts all values to positive
and returns the sum:
=SUMPRODUCT(ABS(A1:A10))

Does that help?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)


"BRob" <del**eteto-hash#rfj1001@ntlworld.com> wrote in message
news:uNBUj.91159$SY5.44650@newsfe13.ams2...
> I've got a column of positive and negative numbers that I want to total in
> two ways :
>
> - net - ie just natural addition which I can do with the SUM formulae
> - absolute - ie the total being based on an assumption that all numbers
> are
> positive
>
> A slightly messy way (because of the design of the spreadsheet) is to
> create
> an extra column of absolute values and sum that.
>
> So my question is 'Is there are formulae I can use to do an absolute sum
> on
> those values directly?'
>
> TIA
>
> Rob
>
>
>
>
>
>