ABS
Returns a number without its sign, so a negative becomes positive.
ABS(<number>)Returns: A non-negative number (a scalar).
What it does
ABS returns the size of a number, ignoring whether it is positive or negative. Minus 250 becomes 250. Finance uses it when the direction of a difference does not matter, only its size: is the variance big enough to explain, regardless of whether it was over or under.
Syntax and arguments
- number: any number, a column, a measure or an expression.
Example
The controller uses a materiality threshold of a fixed dollar amount. Any cost variance larger than that size, in either direction, needs a written explanation.
Absolute Cost Variance = ABS([Actual Cost] - [Budget Cost])
The measure is positive whether spending was over or under budget. A conditional rule can then flag every line where this value is above the threshold, without caring about the sign.
Example: a second, different use
Match bank lines to ledger lines by amount, where a payment and its refund can appear with different signs:
Amount Difference = ABS(Bank[Amount] - Ledger[Amount])
A difference of zero means the amounts agree. A small absolute difference points to a rounding issue or a timing difference that the reconciler can accept.
Things to watch
- ABS removes the sign, so you lose the direction. Keep a signed version next to it if the reader needs to know whether the variance was favorable.
- Summing absolute values is not the same as the net difference. A total of ABS values reports the gross movement, which is often larger than the net.
- For a sign test, use IF or a comparison rather than ABS.
- Check the source for blanks before comparing, because a blank does not give a meaningful difference.
- Do not use ABS to fix data that has the wrong sign. Find out why the sign is wrong first.
In business terms
ABS is the workhorse of variance analysis, reconciliation and materiality testing. It lets a reviewer ask "how big is it" without the direction getting in the way, and it is the basis of a tolerance check such as "match within 5 dollars."