ABS Function (Table of Contents)
ABS in Excel
ABS Function in excel is used to convert any negative number into a positive value. This is one of such function which we can use in any mathematical operation where there are some expectation of getting 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 simply 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 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 the ABS function.
Apply the ABS function in excel to get the absolute numbers without its sign.
16 Online Courses | 23 Hands-on Projects| 140+ Hours| Verifiable Certificate of Completion
4.8
View Course
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 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 this 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, variance percentage automatically converted to positive percentages.
Example #4
Without using ABS, we can convert it into a positive number also. These are smart tricks of using excel.
- Convert negative numbers by multiplying with -1.
- Use Paste special as an 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 the 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 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 ABS Function along with practical examples and downloadable excel templates. You can also go through our other suggested articles –
