Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using sum function with absolute numbers
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/ |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using sum function with absolute numbers
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/ |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using sum function with absolute numbers
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using sum function with absolute numbers
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/ |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using sum function with absolute numbers
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/ |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using sum function with absolute numbers
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/ |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using sum function with absolute numbers
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/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Turning forumula results as absolute numbers | Excel Discussion (Misc queries) | |||
Can I change a column of calculated numbers to absolute numbers? | Excel Discussion (Misc queries) | |||
How do I get the maximum absolute value of a range of numbers? | Excel Discussion (Misc queries) | |||
sum absolute values of a column of numbers in Excel | Excel Worksheet Functions | |||
can excel report scientific values rather than absolute numbers? | Excel Discussion (Misc queries) |