Excel Absolute Value (Table of Contents)
Absolute Value in Excel
Absolute Value in Excel can be calculated using the ABS function, which is available under the category of Math and Trig in Insert function. Absolute Value is the positive form of any negative value whether is it an integer number or decimal number. To use the ABS function, go to the Insert function option from the Formula menu tab in Excel and select ABS function, or else we can directly select ABS function by going in the edit mode of any cell. Then select those cells that contain negative values as per syntax. We will see, it would get converted into positive absolute value.
Absolute Formula in Excel
Below is the Absolute Formula in Excel :
The Absolute Value Formula in excel has one argument:
- Number – which is used to get the absolute value of the number.
Methods of Absolute Functions in Excel
In Microsoft excel, the ABS function comes under the category of Math and Trigonometric, where we can find the Math and Trigonometric in the Formula menu; we will see how to use the ABS function by following the below steps.
- First, go to the Formulas option.
- In the formula menu, we can see the Math & Trig option.
- Click on the Math & Trig option so that we will get the list of functions which is shown in the below screenshot.
- Click on the first function called ABS.
- We will get the ABS function dialogue box as shown below.
- Select the negative number on the cell as shown in the below screenshot.
- Click OK
- The given negative number will be returned as an absolute positive number.
How to Use Absolute value in excel?
Absolute Value in Excel is very simple and easy to use. Let’s understand the working of excel Absolute Value with some examples.
Excel Absolute Value – Example #1
In this example, we are going to see how to use the ABS function by following the below steps.
Consider the below example, which has some random positive and negative numbers.
Now we are going to use the ABS function, which will return the positive number by following the below procedure.
4.9 (8,469 ratings)
View Course
- Click on the Output column.
- Use ABS formula as =ABS(A2) as shown in the below screenshot.
- Press ENTER; we will get the output as follows.
- In the above screenshot, the first row contains a positive number, so there is no difference, and the ABS function returned the absolute value of the number.
- Drag down the formula for all the cell
- We will get the below output as follows.
- As we can see that in the second row, we have a negative number; by using the ABS function, we have converted a negative number to a positive number, and the positive number remains unchanged.
- At last, we can see the VALUE error because the ABS function will throw a VALUE error If the supplied argument is non-numeric.
Excel Absolute Value – Example #2
Using Addition in ABS function
In this example, we are going to see how the ABS function work using SUM. Consider the below example, which has product wise sales figure for three months.
In the above screenshot, we can see that the grand total has been calculated using the SUM formula.
Let us use the ABS function and check for the final grand value by following the below steps.
- First, click on cell C2.
- Insert the ABS function formula as =ABS(B2) shown below.
- Click ENTER.
- The absolute function checks for the number, and it will return the absolute number.
- We will get the output as follows, as shown in the below screenshot.
- Drag down the formula for all the cells so that we will get the final out output as follows.
- As we can see in the above screenshot, the ABS function returned the absolute number and converted the negative number to positive numbers.
- We can notice that the grand total value has a huge difference after using the ABS function because SUM will add the positive numbers and subtract the negative numbers and give the end result.
- Here ABS function converted all negative numbers to positive numbers, and we got the grand total with a positive number.
- Do the same operation and apply the ABS function to the E and H column, and we will get the below result as follows.
In the above result, we can see the Grand total difference after applying the ABS function.
Excel Absolute Value – Example#3
Using Subtraction in ABS function
In this example, we are going to see how the ABS function works in subtraction. Let’s consider the below example, which shows the month-wise sales data of each employee and the difference.
We can notice that we got the difference minus sign figure for the salesperson MAXWELL and the salesperson SMITH in the above screenshot. We have used the subtraction formula as Jan sales value – Feb sales value as shown in the below screenshot.
Here we have used the formulation as =B4-C4 as we cannot subtract lower value with higher value, and while doing sales analysis, this report will go wrong because of the negative figure and also we can see that Average also seems to be wrong due to the negative figure. In order to overcome these errors, we can use the ABS function by following the below steps.
- Click on the D column cell named Difference.
- Use the ABS function as =ABS (B3-C3) will check for the number and return the absolute value shown in the below screenshot.
- Click ENTER, and we will get the output as shown below.
- Drag down the formula for all the cells so that we will get the final output as shown.
- This is the correct way of reporting that the ABS function converted the negative number to a positive number, which gives the exact report.
In the above comparison result, we can see the difference that before applying the ABS function, we got some different figures which is actually an incorrect way of doing subtraction.
We can notice that after applying the ABS function, it returned the absolute value, and we can see the changed in Grand total where it was 735 before and after applying the ABS function, we got the Grand total as 825.
We can notice the same difference in Average where it was 183.7 before and after applying the ABS function; we got an average of 206.25, which is the exact average of the sales report.
Things to Remember
- ABS function in excel normally returns the absolute values. Which converts negative numbers to positive numbers
- While using the ABS function, we can see that positive numbers are unaffected.
- ABS function in excel will throw a #VALUE! error if the supplied argument is non-numeric.
- While using the ABS function, make sure that all arguments are numeric values.
Recommended Articles
This has been a guide to Absolute Value in Excel. Here we discuss how to use the Absolute Value in Excel along with practical examples and a downloadable excel template. You can also go through our other suggested articles –
