Ignore Negative Sign

ABS Function

By Neale Blackwood
No Excel doesn’t have a safe breaking system like your car, but it does have a function that allows you to remove negatives from numbers easily. It’s also useful to find the difference between numbers ignoring whether it is a negative or a positive difference. ABS is short for absolute value which is a mathematical concept that is the numerical value without regard to the sign. So that -5 has an absolute value of 5.

The syntax (layout) of the ABS function is simple =ABS(number) the number can be a cell reference, a function or a formula that returns a number. If cell A1 contained -15 then =ABS(A1) would display 15. If A1 contained text then a #VALUE! error is displayed.

You can use this to compare two columns. Column A has the system stock levels and column B has the stock take count levels of various stock items. If you summed the difference between Column A and B the positive and negative variations may cancel out over the whole list. If you used =ABS(A1-B1) in column C and summed it, then you would see the total number of units difference and that may be a better gauge to the accuracy of the stock count.

If you need an absolute sum of a range (A1:A1000) that already has positive and negative numbers you can use the formula =SUMPRODUCT(ABS(A1:A1000)).