Excel Absolute Value (Table of Contents)
- Absolute Value in Excel
- Absolute Formula in Excel
- Methods of Absolute Functions in Excel
- How to Use Absolute value in excel?
Absolute Value in Excel
In Microsoft excel ABSOLUTE is a built-in function which returns the absolute value of a number (number without its sign) i.e. it will convert the negative numbers to positive numbers and the positive numbers remain unaffected.
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 ABS function comes under the category of Math and Trigonometric where we can find the Math and Trigonometric in Formula menu, we will see how to use ABS function by following the below steps
- First, go to the Formulas option.
- In formula menu, we can see the Math & Trig option.
- Click on the Math & Trig option so that we will get the list of function 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 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 ABS function which will return the positive number by following the below procedure.
- 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 positive number so there is no difference and 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 negative number to positive number and the positive number remains unchanged.
- At last, we can see the VALUE error because 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 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.
Now let we use the ABS function and check for the final grand value by following the below steps as follows.
- First, click on cell C2.
- Insert the ABS function formula as =ABS(B2) shown below.
- Click ENTER.
- 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 ABS function returned the absolute number and converted the negative number to positive numbers.
- We can notice that 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 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 ABS function works in subtraction. Let’s consider the below example which shows the month wise sales data of each employee and the difference.
In the above screenshot, we can notice that we got the difference minus sign figure for the salesperson MAXWELL and for the salesperson SMITH because 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.
- Now, this is the correct way of Report that 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 figure 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 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 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 downloadable excel template. You can also go through our other suggested articles –
