ABS
Returns the absolute value of a number (its value without the sign).
ABS(number)Arguments
numberThe number to get the absolute value of
=ABS(-5)Returns 5 (removes negative sign)
=ABS(A1-B1)Gets absolute difference between cells
=ABS(5)Positive numbers stay the same
- •Useful for calculating differences regardless of direction
- •Always returns a positive number or zero
- •Common in variance and error calculations
- •Using ABS to 'fix' a formula producing unexpected negative numbers instead of investigating why the number is negative in the first place - ABS masks the sign without addressing the underlying calculation
- •Applying ABS to a difference calculation when the actual sign matters for later logic, unintentionally losing information about which value was larger
- •Assuming ABS works on text that merely looks numeric - it requires a genuine number and returns a #VALUE! error on unconverted text
SIGNReturns just the sign of a number (-1, 0, or 1) rather than stripping it entirely like ABS.SUMPRODUCTOften paired with ABS inside an array formula to sum absolute differences between two ranges.IFSometimes used instead of ABS when the sign itself needs to control different logic rather than simply being removed.What does ABS actually do?
It returns a number's absolute value - the distance from zero, ignoring sign. ABS(-5) and ABS(5) both return 5.
Should I use ABS to hide a negative result I don't want?
Only if the sign is genuinely irrelevant to your calculation. If a negative number signals a real issue, like a budget overrun, ABS will hide that signal rather than fix it.
Why does ABS return #VALUE!?
The input isn't a real number - it's likely text that looks numeric but hasn't been converted, which VALUE or NUMBERVALUE can fix first.
Need to translate a formula using ABS?
Use our translator to convert your complete formula