Excel ABS Function: Get a Number Without Its Sign
Returns a number with any minus sign removed.
ABS strips the sign off a number: =ABS(-21) is 21 and =ABS(17) is 17. It is a one-line function with no options, and its usefulness is entirely about the question it lets you ask — how far apart are these, regardless of which is bigger?
That is the variance case. Comparing forecast against actual, the difference matters but its direction often does not: being 200 under is as wrong as being 200 over. Wrapping the subtraction in ABS turns a signed difference into a magnitude, and a column of magnitudes can be averaged or summed into a single accuracy figure.
It is also the shortest way to write a tolerance check. =ABS(A2 - B2) <= 0.5 asks whether two values agree closely enough, which is far clearer than testing both directions separately.
Syntax
=ABS(number)Arguments
numberRequired- The value to strip the sign from. Positive numbers and zero come back unchanged.
The example data
Headers in row 1, data in A2:D6. C4 is blank and C6 holds text.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Sensor | Reading | Calibration | Batch |
| 2 | North inlet | 17 | 3 | 12 |
| 3 | South inlet | -4 | 5 | 7 |
| 4 | Header tank | 63 | 12 | |
| 5 | Overflow | 8 | 2 | 9 |
| 6 | Return line | -21 | n/a | 7 |
Worked examples
=ABS(B3)Result: 4
The -4 reading as a magnitude.
=ABS(B6)Result: 21
The largest deviation from zero, once direction is discarded.
=ABS(B2 - B5)Result: 9
The gap between two readings, which is the same 9 whichever way round you subtract.
=ABS(B3 - B4) <= 5Result: FALSE
A tolerance check. The two readings differ by 67, well outside a tolerance of five.
Now practise it
Reading about a formula is not the same as writing one. Open this function's exercise and type it into a real grid — you get instant feedback on exactly which cell is wrong and why.
Common errors and how to fix them
#VALUE!
Why it happens: The argument is text rather than a number.
How to fix it: Numbers stored as text need converting first. ABS will not coerce them.
The total looks too large
Why it happens: Summing absolute values removes the cancelling-out that signed values do. A +10 and a -10 sum to 0 signed and 20 absolute.
How to fix it: That is usually the intent when measuring error. Be explicit about which you mean when reporting it.
Wrapping the whole formula hides a sign problem
Why it happens: ABS around a calculation that is coming out negative for the wrong reason makes the bug invisible.
How to fix it: Apply ABS to the specific difference you want as a magnitude, not to a whole chain of arithmetic.
Tips worth knowing
- Mean absolute error across a column: =AVERAGE(ABS(forecast - actual)) as a dynamic array, or SUMPRODUCT(ABS(...))/n in older versions.
- =ABS(a - b) <= tolerance is the readable way to write a two-sided comparison.
- SIGN returns just the direction — -1, 0 or 1 — and pairs with ABS when you need to split a number into both parts.
- ABS does nothing to a positive number, so it is safe to apply across a mixed column.
Frequently asked questions
How do I calculate the difference between two numbers regardless of order?
=ABS(A2 - B2). Subtracting in the other order gives the same magnitude, so you never need to work out which value is larger first.
How do I average the error in a forecast?
Take the absolute difference per row and average those: =AVERAGE(ABS(B2:B100 - C2:C100)) in Microsoft 365, or =SUMPRODUCT(ABS(B2:B100-C2:C100))/COUNT(B2:B100) in older versions. Averaging signed differences instead lets overs and unders cancel and reports an accuracy that is not real.
What is the difference between ABS and SIGN?
ABS returns the size and discards the direction; SIGN returns the direction (-1, 0 or 1) and discards the size. Multiply them together and you get the original number back.
Related functions
Guides that use it
Longer reads where this function does real work in a real sheet.