Adding Positve and Negative Numbers

Hunter · Apr 19th, 2002, 05:05 AM

Hi

I would appreciate it if anyone could advise on how I could add a column of positve and negative numbers, so that the end result is the value of the total of the absolute number?

-55
85
-52
=192

Thanks

eliW · Apr 19th, 2002, 05:44 AM

Hunter,
Just enter:
=SUM(ABS(A1:A3))
Array enter by pressing ctrl+shift+enter
Eli

Hunter · Apr 19th, 2002, 07:10 AM

Hi Eli

Thank you so much for your wonderful help. It works.

IML · Apr 19th, 2002, 07:13 AM

A non-array solution would be
=SUMIF(A1:A3,">0")-SUMIF(A1:A3,"<0")

I would suggest going with eliW's answer as long you don't notice any performance issues (ie your have to use the formula many, many times)

Hunter · Apr 19th, 2002, 07:19 AM

Hi IML

Thanks for your advice too. Your method works too and I would adopt your approach, as I do have some performance issues. I need to apply this formula across 10 columns actually.

Best regards
Hunter

[ This Message was edited by: Hunter on 2002-04-19 06:19 ]

Adcb · Apr 19th, 2002, 07:45 AM

Or :-

=SUM(A1:A3)-SUMIF(A1:A3,"<0")*2

Hunter · Apr 19th, 2002, 08:10 AM

Hi Adcb

Thanks a lot.

Regards
Hunter

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 19th, 2002, 05:05 AM	#1
Hunter New Member Join Date: Apr 2002 Posts: 4	Hi I would appreciate it if anyone could advise on how I could add a column of positve and negative numbers, so that the end result is the value of the total of the absolute number? -55 85 -52 =192 Thanks

Apr 19th, 2002, 05:44 AM	#2
eliW Board Regular Join Date: Mar 2002 Posts: 1,805	Hunter, Just enter: =SUM(ABS(A1:A3)) Array enter by pressing ctrl+shift+enter Eli

Apr 19th, 2002, 07:10 AM	#3
Hunter New Member Join Date: Apr 2002 Posts: 4	Hi Eli Thank you so much for your wonderful help. It works.

Apr 19th, 2002, 07:13 AM	#4
IML MrExcel MVP Join Date: Feb 2002 Location: Denver, CO Posts: 1,744	A non-array solution would be =SUMIF(A1:A3,">0")-SUMIF(A1:A3,"<0") I would suggest going with eliW's answer as long you don't notice any performance issues (ie your have to use the formula many, many times)

Apr 19th, 2002, 07:19 AM	#5
Hunter New Member Join Date: Apr 2002 Posts: 4	Hi IML Thanks for your advice too. Your method works too and I would adopt your approach, as I do have some performance issues. I need to apply this formula across 10 columns actually. Best regards Hunter [ This Message was edited by: Hunter on 2002-04-19 06:19 ]

Apr 19th, 2002, 07:45 AM	#6
Adcb Join Date: Mar 2002 Posts: 12	Or :- =SUM(A1:A3)-SUMIF(A1:A3,"<0")*2

Apr 19th, 2002, 08:10 AM	#7
Hunter New Member Join Date: Apr 2002 Posts: 4	Hi Adcb Thanks a lot. Regards Hunter