SUM of ABS values

[Steve Almond]

I have a column of numbers - some positive, some negative. I want to
calculate the SUM of the absolute values of the column.
I tried:

=SUM(ABS(A1:A15)) and
=SUM(ABS(A1):ABS(A15))

Neither works. Of course, I can simply make a new column of the absolute
values and sum that column. But, can I do it directly?

Thanks,

Steve

 

[Andy B]

Hi

Use your first formula and array enter it. That means that instead of
pressing Enter after editing/entering the formula, use Ctrl Shift and Enter.

Andy.

 

[Norman Harker]

Hi Steve!

Enter as an array formula:

=SUM(ABS(A1:A15))
Enter by pressing and holding down Ctrl + Shift and then pressing
Enter.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Andy Pope]

Hi Steve,

Try entering as an array formula, which is done by pressing
CTRL+SHIFT+ENTER. This will added the curly brackets to your formula.

=SUM(ABS(A1:A15))

Cheers
Andy

 

[Steve Almond]

Thanks to all who answered.
Works exactly as described. But, How did you know this was an array
'problem'? What is the feature of this that says "Use an array formula"?

Steve

 

[Norman Harker]

Hi Steve!

Re: What is the feature of this that says "Use an array formula"?

I suppose it's that you need to perform the same operation (take the
ABS) on each of the cells in the range.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Andy B]

Steve

I suppose you just get to learn these things! The fact that ABS() only works
on a single cell usually and we are trying to use it on more than one (an
array) was a bit of a clue!

Andy.