Excel ABS Function
What It Does
The ABS function returns the absolute value of a number.
What Is Absolute Value? + Examples
Using simple language, absolute value of a number is the same number without a minus sign.
For some people it is easiest to think of absolute value as a number’s distance from zero.
In other words, if the input number is negative, the ABS function returns the corresponding positive number, for example:
ABS(-5) = 5
If the input is non-negative, it returns the same number, for example:
ABS(8.2) = 8.2
ABS(0) = 0
Using math language:
ABS(x) = -x for x<0
ABS(x) = x for x>=0
For more detailed explanation of absolute value, see Wikipedia:
Excel ABS Function Arguments
The ABS functions takes only one argument – the number for which you want to get the absolute value.
The syntax is:
As with other Excel functions, the argument can be given directly as a value, e.g. ABS(5), or as a formula, e.g. ABS(3+2), and can include references to other cells, e.g. ABS(A2) or ABS((A2+A3)/2).
In any case, the input must be a numeric value. Absolute value of text does not make sense, therefore you get a #VALUE! error, for example
ABS(“some text”) = #VALUE!
In Excel, the ABS function does work with inputs which are technically numeric values for Excel, even when they might seem to be logical or non-numeric values to humans. For example, if you want to use ABS with the following for some reason…
ABS(TRUE) = 1 because the logical value TRUE is treated as number +1 in Excel
ABS(FALSE) = 0 because the logical value FALSE is treated as number 0 in Excel
ABS also works with dates and times, because these are also actually numeric values in Excel. For example:
ABS(DATE(2015,10,2)) = 42279
ABS(TIME(10,42,6)) = 0.44590278
Excel ABS Function Errors
The most common error you will get with ABS function is the #VALUE! error when input is not a numeric value, as discussed above.
If you get some other error, such as #DIV/0! or #NUM!, the problem is not with the ABS function itself, but with the input (if you removed the ABS function, the error would most likely stay the same). For example:
ABS(5/0) = #DIV/0! because 5/0 = #DIV/0! because you can’t divide 5 (or anything else) by zero.
ABS(99999999^99999999) = #NUM! because 99999999^99999999 (99,999,999 to the power of 99,999,999) = #NUM! because the number is too big to be calculated in Excel.
ABS Function Examples Summary
Support in Excel Versions
ABS is a very basic function and works in all versions of Excel.