How to Get Absolute Value in Excel

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.

how to get absolute value in excel

Step 2: In our case the cell is F5. We should write the formula given below to the cell.

=ABS(E5)

Here, E5 is the reference for the input number.

get absolute value in excel use ABS

We can also select the ABS function from Formulas > Math&Trig > ABS in the ribbon.

absolute value using 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.

get absolute value result

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.

=SUM(ABS(E6:E8))

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.

get absolute value with sumif function

Step 2: We have to enter the cell and write the formula given below.

=ABS(SUMIF(B6:B8,">0")+SUMIF(C6:C8,"<0"))

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.

=ABS(SUMPRODUCT(E6:E9))

Here, E6:E9 is the range of cells whose absolute values we want to sum.

get absolute value abs with sumproduct

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.

Conclusion

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.

Mehedi Hasan Shimul

Mehedi Hasan Shimul

Hi! I am Mehedi Hasan Shimul. As I am an Engineer solving different problems with the help of Excel amuses me. I write Excel related different problem solving articles here. Hope it will help you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo