Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing a Column - Net and Absolute Values
I've got a column of positive and negative numbers that I want to total in
two ways : - net - ie just natural addition which I can do with the SUM formulae - absolute - ie the total being based on an assumption that all numbers are positive A slightly messy way (because of the design of the spreadsheet) is to create an extra column of absolute values and sum that. So my question is 'Is there are formulae I can use to do an absolute sum on those values directly?' TIA Rob |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing a Column - Net and Absolute Values
Suppose the numbers are in column A, you can try this array* formula:
=SUM(ABS(A1:A10)) Adjust the range to suit. * An array fromula must be committed using the key combination of CTRL- SHIFT-ENTER (CSE) instead of the usual ENTER. If you do this correctly then Excel will wrap curly braces { } around the formula when viewed in the formula bar - you must not type these yourself. If you subsequently amend/edit the formula you must use CSE again. Hope this helps. Pete On May 8, 12:42*pm, "BRob" wrote: I've got a column of positive and negative numbers that I want to total in two ways : - net - ie just natural addition which I can do with the SUM formulae - absolute - ie the total being based on an assumption that all numbers are positive A slightly messy way (because of the design of the spreadsheet) is to create an extra column of absolute values and sum that. So my question is 'Is there are formulae I can use to do an absolute sum on those values directly?' TIA Rob |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing a Column - Net and Absolute Values
With your list of pos/neg values in A1:A10
This formula converts all values to positive and returns the sum: =SUMPRODUCT(ABS(A1:A10)) Does that help? Post back if you have more questions. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "BRob" wrote in message ... I've got a column of positive and negative numbers that I want to total in two ways : - net - ie just natural addition which I can do with the SUM formulae - absolute - ie the total being based on an assumption that all numbers are positive A slightly messy way (because of the design of the spreadsheet) is to create an extra column of absolute values and sum that. So my question is 'Is there are formulae I can use to do an absolute sum on those values directly?' TIA Rob |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing a Column - Net and Absolute Values
One way (Array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):
=SUM(ABS(A1:A10)) In article , "BRob" wrote: I've got a column of positive and negative numbers that I want to total in two ways : - net - ie just natural addition which I can do with the SUM formulae - absolute - ie the total being based on an assumption that all numbers are positive A slightly messy way (because of the design of the spreadsheet) is to create an extra column of absolute values and sum that. So my question is 'Is there are formulae I can use to do an absolute sum on those values directly?' TIA Rob |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing a Column - Net and Absolute Values
For the absolute sum try
=SUM(IF(ISNUMBER(ABS(A1:A20)),ABS(A1:A20),FALSE)) Which is an aeeay so commit with Ctrl+Shift+Enter The inner ISNUMBER isn't strictly necessary but takes care of any odd error values. Mike "BRob" wrote: I've got a column of positive and negative numbers that I want to total in two ways : - net - ie just natural addition which I can do with the SUM formulae - absolute - ie the total being based on an assumption that all numbers are positive A slightly messy way (because of the design of the spreadsheet) is to create an extra column of absolute values and sum that. So my question is 'Is there are formulae I can use to do an absolute sum on those values directly?' TIA Rob |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
summing first 'n' values in a column or row | Excel Worksheet Functions | |||
Summing values in a column if the date in another is of a certain month | Excel Worksheet Functions | |||
summing last values in column | Excel Worksheet Functions | |||
Summing one column if two other columns' values appear in other sh | Excel Worksheet Functions | |||
sum absolute values of a column of numbers in Excel | Excel Worksheet Functions |