Updated August 16, 2023
Absolute Value in Excel
Absolute Value in Excel is the positive form of any negative value, whether it is an integer number or a decimal number. It can be calculated using the ABS function, available under Math and Trig in the Insert function. 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 the ABS function by going into 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 a 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 will see how to use the ABS function by following the steps below.
Consider the below example, which has some random positive and negative numbers.
Now we will use the ABS function, which will return a positive number by following the procedure below.
- Click on the Output column.
- Use the 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 returns the absolute value of the number.
- Drag down the formula for all the cell
- We will get the below output as follows.
- As we see in the second row, we have a negative number; 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 will see how the ABS function works using SUM. Consider the below example, which has product-wise sales figure for three months.
The above screenshot shows that the grand total has been calculated using the SUM formula.
Let us use the ABS function to check for the final grand value by following the steps below.
- 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 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 following result.
In the above result, we can see the Grand total difference after applying the ABS function.
Excel Absolute Value – Example#3
Using Subtraction in the ABS function
In this example, we will 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 a lower value with a higher value. While doing sales analysis, this report will go wrong because of the negative figure, and also we can see that the Average also seems to be wrong due to the negative figure. To overcome these errors, we can use the ABS function by following the below steps.
- Click on the D column cell named Difference.
- Using 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 to 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 that before applying the ABS function, we got some different figures, which is an incorrect way of subtraction.
After applying the ABS function, we can observe that it returned the absolute value. Notably, there is a noticeable change in the Grand total. Initially, it was 735, but after applying the ABS function, the Grand total became 825.
We can notice the same difference in the Average, where it was 183.7 before and after applying the ABS function; we got an average of 206.25, the exact average of the sales report.
Things to Remember
- The 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, ensure 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, practical examples, and a downloadable Excel template. You can also go through our other suggested articles –
