ABS Function (Table of Contents)
ABS in Excel
In excel we all deal with numbers and often we deal with negative numbers. There are scenarios where we need to convert all the negative numbers to positive numbers and we do not know how to do it.
Believe me, there is no rocket science involved in converting those negative numbers to positive numbers.
In this article, I will explain you the simple formula, which can help us to convert all the negative numbers to positive numbers.
ABS stands for absolute. ABS function returns an absolute number without its sign. It can be used as both worksheet function and VBA function in excel.
ABS Formula in Excel
Below is the ABS Formula:
The Formula of 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 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 ABS function.
Apply the ABS function in excel to get the absolute numbers without its sign.
The Result will be:
If you look the positive numbers, it returns the same value. There are no signs for 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 as a positive number.
This is done by using 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 positive number by using ABS function (ABS (A2))
and if the number is not negative
then the result will be shown as “Positive Number”.
Example #3
From the below table calculate the target vs actual report for the sales team. You are provided with target and actual achieved number. You need to calculate 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 givc us variance % value.
The problem with this generic formula is we always get negative numbers if the actual value in less than the targeted value and it affects our variance percentage too. Therefore, to eliminate this negative numbers we can use ABS function to get the absolute values.
I just applied 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, variance percentage automatically converted to positive percentages.
Example #4
Without using ABS, we can convert it into positive number also. These are smart tricks of using excel.
- Convert negative numbers by multiplying with -1.
- Use Paste special as option.
Convert Negative Numbers by Multiplying with -1.
The basic of mathematics is lies in understanding the signs multiplication and those sings multiplication are as follows.
Since we are trying to convert negative numbers to positive 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 with -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 below steps.
Step 1: Copy the value -1 which we have entered in the cell D3.
Step-2: Now select all the negative values.
Step-3: Open paste special dialogue box using ALT + E + S and select Multiply option.
Step-4: Now press ok. All the negative values converted to positive values in the same range and the result will be same as the below image.
Things to Remember About ABS Function.
- ABS can accept only numeric values. Anything other than 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 ABS Function along with practical examples and downloadable excel templates. You can also go through our other suggested articles –
