The ABS function will return the absolute value of any number, converting any negative number into a positive number, essentially removing the sign of all numbers.
Contents:
Syntax
= ABS([number])number = any numerical value, can be hardcoded, a cell reference, or a calculation
Explanation
This function is part of the "Math and Trigonometry" function group. Its sole purpose is to take numerical values and return the positive, absolute value.
An absolute value is always positive, and represents how for away a number is from zero, rather than a specific positive or negative number.
Only numerical values will work inside this function. If a non-numerical number (such as a text string) is used, a #VALUE error will be returned instead.
Examples
1. How to Return Positive Absolute Values of any Number
This is the most basic use of the ABS function. By feeding in a number into the function, we get back a positive number, converting all negatives to positive. Input can be hardcoded, a cell reference, or hold a calculation
= ABS(B3)
2. How to Find the Square Root of Negative Numbers
Normally, if you try to take the square root of a negative number using the SQRT function, Excel will throw a #NUM! error, as in mathematics, the square root of a negative number doesn't exist. But what if you still need to calculate the square root of a number regardless if it's positive or negative.
Using the below formula throws errors when we try to take the square root of the negative numbers.
= SQRT(B3)
Instead, if we add on the ABS function, we can take the square root of any number, regardless if it is positive or negative.
= SQRT(ABS(B3))
3. How to calculate Difference in Numbers using the ABS Function
In some calculations, the actual difference or distance between numbers is more important than a positive or negative number.
For instance, say we were calculating the difference cars were traveling over and under the speed limit.
A car driving -15mph under the speed limit doesn't make sense, the difference between a car going 40mph and a speed limit of 55mph, is 15mph.
= ABS(C3-D3)
4. How to calculate Total Distance Traveled Using the ABS Function
Like the previous example, when looking at total distance traveled from a specific point, you would not want to sum up negative movement.
Lets say a train leaves a station and travels 5 miles away and then returns. The train would have traveled +5 miles away, then -5 miles back to return to the same location. But, the train traveled 10 miles total, we can't track distance by adding -5 to +5, that would leave us with 0 miles traveled.
Instead, we need to use the absolute value of all distance traveled to figure out the total distance traveled.
= ABS(C3) + ABS(D3)
5. How to Sum only Negative or Positive Numbers with the ABS Function
If we wanted to add up all of the negative sales we've had over the past 5 years, we can't just add negative numbers together, as you cant have negative negative sales. Instead, if we take the absolute value of the negative sales, we'll be left with actual amount of negative sales.
In this formula, if the cells in the range C3:C7, are less than 0, than we are taking the absolute value of those numbers, and summing them up.
= SUM(IF(C3:C7<0, ABS(C3:C7), 0))Similarly, if we wanted to sum only positive numbers, we would use:
= SUM(IF(C3:C7>0, ABS(C3:C7), 0))