FormuladrillABS
ABS(number) returns the absolute value of a number: -5 becomes 5 and 5 stays 5. Use it for the size of a difference regardless of direction, for variance checks (is the gap within a tolerance) and to turn a column of negative amounts positive.
Syntax
ABS(number)| Part | What it is |
|---|---|
number | The value, cell or formula whose sign you want removed. |
Examples
=ABS(-5)Result: 5
=ABS(A2 - B2)Result: The difference between A2 and B2 as a positive number, whichever is larger
=IF(ABS(A2 - B2) <= 0.01, "OK", "Check")Result: OK when the two amounts differ by at most one cent
A typical reconciliation check.
=SUMPRODUCT(ABS(A2:A10 - B2:B10))Result: Total absolute deviation between the two columns
Common mistakes
- ABS on text gives #VALUE!. Make sure the cell holds a number, or wrap it in IFERROR.
- Using ABS to turn credits into debits loses the sign information; keep the original column and put ABS in a new one.
- Confusing ABS with absolute references ($A$1): ABS is a function on values, the dollar signs lock a cell address.
Practice it
The drills on Formuladrill check your answer on live data, free, no account needed.
Start practicingQuestions
How do I make negative numbers positive in Excel?
=ABS(A2) and fill down. To change them in place, type -1 in a spare cell, copy it, select the negatives and use Paste Special, Multiply.
How do I get the difference between two numbers regardless of sign?
=ABS(A2 - B2). Without ABS the result is negative when B2 is bigger.
Related
- ROUNDHow to round in Excel with ROUND, ROUNDUP, ROUNDDOWN and MROUND: to 2 decimals, to the nearest hundred and nearest 5, and why formatting is not rounding.
- MODHow the Excel MOD function returns the remainder of a division, with examples for odd and even, every nth row, the decimal part of a number and the sign rule.
- SUMPRODUCTHow the Excel SUMPRODUCT function multiplies ranges and sums the result, with examples for quantity times price, weighted average and sums with conditions.
- IFHow the Excel IF function returns one value when a test is true and another when false, with pass or fail, blank cell, nested IF and IF with AND examples.