[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[_2_]]

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

--


Regards,


Peo Sjoblom

"mebsmith" wrote in message
...
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/

[Ron Rosenfeld]

On Mon, 29 Sep 2008 09:36:02 -0700, mebsmith
wrote:

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

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"mebsmith" wrote in message
...
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/

[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


Replace @mailinator.com with @tiscali.co.uk


"mebsmith" wrote in message
...
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/

[mebsmith]

Thanks everyone



"Sandy Mann" wrote:

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


Replace @mailinator.com with @tiscali.co.uk


"mebsmith" wrote in message
...
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/

[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")

--
Thanks,
Shane Devenshire


"mebsmith" wrote:

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/