Using sum function with absolute numbers

[mebsmith]

I am trying to get the absolute average of a column of positive and negative
numbers, so i am trying to gather the absolute sum of the numbers using the
following:

=SUM(ABS($F3:$F216))

however it returns a number (1) that isn't the correct answer. However when
i hit the function key in the formula bar the correct answer is displayed
under the inputs.

What am I doing wrong? Please help/

 

[Peo Sjoblom]

You need to enter it with ctrl + shift & enter since it is an array formula

--


Regards,


Peo Sjoblom

 

[Ron Rosenfeld]

I am trying to get the absolute average of a column of positive and negative
numbers, so i am trying to gather the absolute sum of the numbers using the
following:

=SUM(ABS($F3:$F216))

however it returns a number (1) that isn't the correct answer. However when
i hit the function key in the formula bar the correct answer is displayed
under the inputs.

What am I doing wrong? Please help/

The formula is an array formula. So you must hold down <ctrl><shift> when you
hit <enter> to enter the formula. If you do this correctly, Excel will place
braces {...} around the formula.
--ron

 

[Bernard Liengme]

You need to commit your formula with ctrl+shift+enter to make it an array
formula
Double click the cell, then hold down shift and control keys now tap the
Enter key
best wishes

 

[Sandy Mann]

Array enter the formula by holding down Ctrl & Shift while you press Enter.
This will automatically put curly braces { } around the formula and it will
then correctly calculate.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[mebsmith]

Thanks everyone

Array enter the formula by holding down Ctrl & Shift while you press Enter.
This will automatically put curly braces { } around the formula and it will
then correctly calculate.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[ShaneDevenshire]

Hi,

Well, as you can see there is a common solution, but here are two others
that don't require SHIFT+CTRL+ENTER

=SUMPRODUCT(ABS($F3:$F216))

or

=SUMIF($F3:$F216,">0")-SUMIF($F3:$F216,"<0")