When we work with data sometimes we may need to take the absolute value of a number. In Excel, we can easily get the absolute value of any number. We can use the absolute value of the number in many ways in Excel. In this article, we will show how to do that.
What Does Absolute Value Mean?
Absolute value means the distance of a number from zero in the number line. Whether the number is positive or negative, the absolute value is always positive. The absolute value of 0 and positive numbers remain unchanged.
Download Practice Workbook
You can download the practice workbook from here
4 Easy Methods to Get Absolute Value in Excel
We can get absolute value and use it in many ways while working in Excel. 4 such simple methods are described below to get absolute value and the use of absolute value in Excel.
Method 1: Use ABS Function to Get Absolute Value
In Excel, there’s an in-built function to get an absolute value which is the ABS function. In this method, we will show how to use the ABS function to get the absolute value of a number.
Step 1: At first we have to select and enter the cell where we will store the absolute value.
Step 2: In our case the cell is F5. We should write the formula given below to the cell.
Here, E5 is the reference for the input number.
We can also select the ABS function from Formulas > Math&Trig > ABS in the ribbon.
Step 3: We have to press ENTER.
The absolute value will be there in the cell.
Step 4: We will use the Fill Handle to copy the formula to the cells below.
We can see all the negative numbers changed into positive numbers. The positive number remains unchanged.
Method 2: Getting Absolute Value Using ABS and SUM Functions
We can get the absolute value of a number by simply using ABS and using the value in the SUM function. We will show the method to sum the absolute values of a few numbers.
Step 1: We have to select the cell where we want the sum of absolute values. In our case, the cell is E10.
Step 2: We need to enter the cell and write the following formula in the cell.
Here, E6:E8 is the range of cells for input.
Step 3: We have to enter the formula in the cell.
We can see the sum of absolute values of a few numbers.
Method 3: Using ABS with SUMIF Function
We can also use the ABS function with SUMIF function. Suppose we have two sets of numbers Number Set 1 and Number Set 2. Both of them consist of positive and negative numbers. Suppose we want to get the absolute value of the sum of positive numbers from Number Set 1 and negative numbers from Number Set 2. The steps to do that are described below.
Step 1: We have to select the cell where we want to show the result.
Step 2: We have to enter the cell and write the formula given below.
Here, B6:B8 is the range for Number Set 1 and C6:C8 is the range for Number Set 2. “>0” and “<0” are the criteria for the SUMIF function.
Step 3: We have to press ENTER.
The result will be displayed in the cell. In our case, we added the positive number from Number Set 1 and the negative number from Number Set 2 and the result is 6 (15+18-14-25=6).
Method 4: Combine ABS with SUMPRODUCT Function
We can also use the ABS function with the SUMPRODUCT function if we need it. We will show the steps to use the ABS function with the SUMPRODUCT function.
Step 1: First we will select and enter the cell. In our case, the cell is E10.
Step 2: We enter the formula given below to the cell.
Here, E6:E9 is the range of cells whose absolute values we want to sum.
Step 3: We need to enter the formula into the cell.
We can see the sum of the absolute value.
Uses of Absolute Values
We can use absolute values in many ways. Some of the important uses of absolute values are listed below.
- To find the square root of a negative number.
- To find absolute variance of real data and expected data.
- To compare variance with a fixed tolerance.
Absolute value of a number is very useful in various conditions. We can use the ABS function in Excel to find the absolute value of any number which we can use for further operation. In this article, we have shown 4 simple methods to get absolute value in Excel and use it. Hope this article will help you. Please let us know by comment if you face any problems or if you have any suggestions about the article.