Totaling Absolute Values

[Jtucker10278]

Doing inventory,

Item A is short -3
Item B is over +3

Total amount of inventory +/- is 0 but in reality I am off by 6

I know ABS returns the absolute value of a number and it is not a big deal to add an extra column for ABS Value

But I was wondering if there was a neat trick to totaling the absolute value without the extra column

Thanks

 

[Nine Zero]

you could multiply them both by 1 or -1 depending on what they are and then add them up something like this


=SUM(IF(A1<0,A1*-1,A1),IF(A2<0,A2*-1,A2))

assuming your -3 is in A1 and your 3 is in A2

or the ABS way would be

=SUM(ABS(A1),ABS(A2))

 

[Scott T]

Excel 2010
	A	B
8	-3
9	3
10		6
Sheet1

Cell Formulas
Range	Formula
B10	{=SUM(ABS(A8:A9))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[mopp1]

Or if you don't want to enter as an array function using CSE then =SUMPRODUCT(ABS(A1:A10)) will also work (change A1:A10 to your range ID) with just Enter.