ABS
Removes the minus sign from a number: how far off plan you are, the gap between two amounts regardless of direction.
ABS(number)- number
- A number, a cell or a difference like C2-B2.
Return value
The same number without the minus sign: both −5 and 5 give 5.
Examples
Gap from plan
fx
A
B
C
D
E
1
2
3
4
5
6
7
Rep
Plan, $
Actual, $
Gap, $
Johnson
200000
184000
Miller
150000
163400
Davis
210000
214600
Wilson
120000
98400
Sheet1
Details
ABS returns the absolute value of a number, that is, its size without the sign: for −1500 it’s 1500, and for 1500 it’s also 1500.
Most often ABS wraps a difference: =ABS(C2-B2) shows how far actual is from plan, no matter in which direction. It’s a handy way to find the biggest gaps.
Common mistakes
- ABS loses the direction: the result doesn’t tell you whether the plan was beaten or missed. If that matters, keep a plain difference next to it.
- Text instead of a number: ABS returns #VALUE!.