Using sum function with absolute numbers

M

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/
 
P

Peo Sjoblom

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

--


Regards,


Peo Sjoblom
 
R

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
 
B

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
 
S

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
 
M

mebsmith

Thanks everyone



Sandy Mann said:
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
 
S

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top