Updated May 6, 2023
ABS Function (Table of Contents)
ABS in Excel
The ABS Function in Excel converts any negative number into a positive value. It is one such function we can use in any mathematical operation with some expectation of getting a negative value. The values obtained from the ABS function are the absolute values just by removing the minus (-) sign from the calculated value; for this, we need to apply the ABS function in the value or put the formula inside the ABS.
ABS Formula in Excel:
Below is the ABS Formula.
The Formula of the ABS function is very easy, and that includes only one parameter, i.e. number.
Number: This is the required parameter. The number you wish to get is the absolute number without its sign.
For this, the function we can give cell reference, we can enter the number directly, and we can enter the number in double quotes.
How to Use the ABS Function in Excel?
This ABS function is very simple and easy to use. Let us now see how to use the ABS Function with the help of some examples.
Example #1
From the list of numbers, convert all the negative numbers to positive numbers using the ABS function.
Apply the ABS function in Excel to get the absolute numbers without its sign.
The result will be:
If you look at the positive numbers, it returns the same value. There are no signs of positive numbers, so it returns the number as it is.
Example #2
From the above example, we have learned how to convert negative to positive numbers. Now take the same example, but the thing is if the number is negative, convert it to positive and if it is not negative, show it as a positive number.
This is done by using the IF condition. IF condition is checking whether the given number is less than zero or not (If (A<0,). If the number is less than zero
Then it converts the negative number to a positive number by using the ABS function (ABS (A2))
And if the number is not negative.
Then the result will be shown as a “Positive Number”.
Example #3
From the below table, calculate the target vs actual report for the sales team. You are provided with the target and actual achieved number. You need to calculate the variance and calculate the variance percentage.
In order to calculate the variance, we can use the formula as = Actual – Target.
This would give us the variance value.
In order to get the variance % value, we use the formula =Variance / Target *100.
This will be given us a variance % value.
The problem with this generic formula is that we always get negative numbers if the actual value is less than the targeted value, affecting our variance percentage. Therefore, to eliminate these negative numbers, we can use the ABS function to get the absolute values.
I just applied the ABS function before I do the calculation of Actual – Target. The result is converted to the absolute value by ABS.
One more interesting thing here is as soon as we get a positive variance value, the variance percentage is automatically converted to positive percentages.
Example #4
Without using ABS, we can convert it into a positive number also. These are smart tricks for using Excel.
- Convert negative numbers by multiplying them with -1.
- Use Paste Special as an option.
Convert Negative Numbers by Multiplying with -1.
The basis of mathematics lies in understanding the signs of multiplication and those signs of multiplication are as follows.
Since we are trying to convert negative numbers to positive ones, we can use the last method, i.e. Minus * Minus = Plus
By using the above method, convert the below numbers to absolute numbers.
Multiply all these numbers by -1 to get the positive values.
The result will be :
Use Paste Special to Convert Negative Numbers to Positive Numbers.
Enter -1 in one cell and follow the below steps.
Step 1: Copy the value -1, which we have entered in cell D3.
Step 2: Now select all the negative values.
Step 3: Open and paste the special dialogue box using ALT + E + S and select the Multiply option.
Step 4: Now press ok. All the negative values converted to positive values in the same range, and the result will be the same as the below image.
Things to Remember
- ABS can accept only numeric values. Anything other than the numeric value, the error will be #VALUE!
- No changes will happen to positive numbers. Only negative values are converted to positive values.
- VBA code to ABS function is WorksheetFucntion.ABS (-650).
Sub ABS_Example
Dim MyValue as Double
MyValue = Application.WorkSheetFunction.ABS(-650)
Msgbox MyValue
End Sub
Recommended Articles
This has been a guide to ABS Function. Here we discuss the ABS Formula and how to use the ABS Function along with practical examples and downloadable Excel templates. You can also go through our other suggested articles –
