Sum the Absolute Value of a range?

I suspect you'll need to make this a two stage operation:

1. In a new column, use ABS to convert each value to it's absolute value.
2. Sum the second column.

Column B cells have a formula generating a random integer between 7 and 25, that is then multiplied by either 1 or -1 depending on a random fraction's being larger than 0.600. It's only purpose here is to generate a set of mixed positive and negative values. A conditional highlighting rule applies yellow fill to cells containing values less than 0.

Column C has two formulas. C2, and filled down to C10: ABS(B2)

Row 11 is a Footer row. C11 contains this formula:           SUM(C)

B11 contains the equivalent sum formula for that column.

Regards,

Barry

SGIII writes:

=SUMIF(A,">0",A)−SUMIF(A,"<0",A)

Nice!

Though it won't change the result, I'd change   ">"0   to   ">="0   just for completeness. Not as pretty, though.

Regards,

Barry

@Badunit,

No. Either comparison operator can be changed to include the zero value. Including it in the formula simply acknowledges that zero is a possible value, not that it is a "positive" value.

In terms of the result, including zero values is not significant wrt the SUMIF, but will affect the results of other members of the —IF family, such as COUNTIF and AVERAGEIF.

Regards,

Barry

My opinion is Excel is superior for all the things it can do and the speed of calculation (and compatibility with others) but it is so cluttered and clunky and old feeling I hate using it. I don't need most of what Excel can do. I use Numbers almost exclusively.

So now we are assigning positivity to the number zero by putting it into the "positive" ABS formula? Watch your back for angry mathematicians and wikipedia editors.

Just joshing with you. I, too, like all possible cases represented in a formula. IF statements are the usual one where people don't take every case into consideration.

I intentionally omitted the =0 possibility as irrelevant.

In the spirit of Occam's razor, and all that.

SG

"I intentionally omitted the =0 possibility as irrelevant. "

Also allowed, of course. ;-)

Barry

You can use SUMIF to do this in one step, like this:

In A11:

=SUMIF(A,">0",A)−SUMIF(A,"<0",A)

In my example Row 11 is defined as a Footer Row so can put the formula there and refer to the column.

Change A to whatever your range is.

SG