Excel’s ABS function is used to produce a non-negative number from a numeric input.
This is equivalent to converting any real number to an absolute value by removing the sign (+/-) from a number. Having the effect of converting a negative number into a positive number, but leaving positive numbers unaffected.
The absolute value of zero is always zero.
Function Syntax
=ABS(number)
- The number argument is required.
Usage
A typical use case for the ABS function is in identifying the magnitude of difference between 2 numbers.
Depending which of the 2 numbers you take and subtract from the other could create a negative answer in one case and a positive answer in the other. However the numeric value without the sign would be the same in both cases. In this case, where only the size of the difference is required, the ABS absolute function can be used to remove the negative sign to produce a magnitude (or size), regardless of the sign.
Also in mathematics, the square root of any positive number could be a negative or positive value. To avoid selecting an imaginary number, the absolute of the value is used to select the real positive value.
NOTE: In written mathematical form absolute x is written as |x|
Examples
The Absolute value of a positive number remains the same positive number.
The Absolute value of a negative number outputs a positive number.
The Absolute value of zero remains zero.
Percentages and Currencies work as for normal numeric values with Absolute (red font added for clarity). Note that depending on the cell formatting your percentage may display as a decimal.
Calculating the magnitude of the difference of 2 values
When dealing with Currencies, the ABS function works as expected, though you will need to consider formatting output cells accordingly
Naturally the ABS function, like most other Excel functions, can include calculations within them. Here we include the intermediate step directly inside the ABS function, allowing us to remove the column subtracting Value1 from Value2.
Related Topics
- Other Excel Functions
- Other Excel Math Functions
F001