ABS — 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)).

Neale Blackwood is a senior business analyst with Access Analytic Solutions, which provides financial modelling, management reporting services, as well as advanced Excel training. You can email Neale at nblackwood@accessanalytic.com.au