How to Get Absolute Value in Excel (8 Effective Methods)

Absolute value means the actual value of a number without any sign. Mathematically the absolute value is the modulus of a number. In this article, we will show you how to get absolute value in Excel.

There are multiple ways to get absolute value in Excel. Of them, using the ABS function is the simplest way to get absolute value in Excel.

We will also discuss other ways consisting of functions, Pivot Table, Power Query, and VBA to get absolute value in Excel.

Look at the following image to get the brief idea of how to calculate an absolute value in Excel.

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. Absolute values do not contain any sign. Mathematically we can say this is the modulus of any number and represent it as |X|. Here, X can be any positive or negative number and the result will be an absolute value is X without any sign.

Note:

Absolute value and absolute cell reference are two different things. Do not mix up them. The absolute cell reference is a special type of cell addressing in Excel, that does not change by dragging or copying that cell.

How to Get Absolute Value in Excel: 8 Suitable Ways

In this article, we will discuss 8 different ways how to get absolute value in Excel for any number. For that, we will consider the following dataset consisting of the name of the Brand, sales of the present day, and the previous day.

Now, we will find out the variance of sales using the following formula on cell E5 and after that use the Fill Handle icon to execute that formula in the rest of the cells.

We get both positive and negative values in the Variance column. We will show how to get the absolute value of variance in the below discussion.

1. Use of Excel ABS Function to Get Absolute Value

The ABS function is most widely used to get absolute value in Excel. This is the easiest way for getting absolute value.

• Go to cell F5 and insert the following formula based on the ABS function.

• Then drag the Fill Handle icon downwards.

The ABS function always returns the absolute value of any given reference.

Read More: Opposite of ABS Function in Excel

2. Apply Condition Using the IF Function to Get Absolute Value

In this section, we will apply a condition using the IF function to determine whether the given number is positive or negative. If the given number is smaller than zero means negative, it will return the opposite positive value, otherwise will remain the same.

• Insert the following formula on cell F5 and pull the Fill Handle icon,

We get the absolute value for variances in column F.

3. Combination of SQRT and POWER Functions to Find Absolute Value

This is another way to get absolute value in Excel. Here, we will use the combination of the SQRT and POWER functions. The SQRT function returns the positive square root value, and the POWER function results based on a specific power.

In this section, we will consider power 2 of the given number. After that, the SQRT function returns the square root value which is the absolute value of the given number. So, raising the power of a number by 2 and then calculating the square root of the result will return the absolute value of the initial number.

• Input the following formula on cell F5 and drag the Fill Handle icon.

We can also use the circumflex (^)” operator in place of POWER function and the formula will look like this.

4. Excel MAX Function to Get Absolute Value by Comparing the Opposite Value

We can also use the MAX function to determine the absolute value of any number. Here, 1st we multiply the given number by -1. Then, the MAX function compares the given number and its opposite number multiplied by -1 and returns the maximum number of them.

• Apply the formula below on cell F5 and pull the Fill Handle icon.

5. Use of SIGN Function to Multiply the Given Number with its Sign for Absolute Value

The SIGN function determines the sign of a number. For positive and negative numbers, it will return 1 and -1 respectively. Here, we used the SIGN function to get the sign of the given number, then multiply the given number by that sign. So, whatever the number is positive or negative always returns a positive number.

• Move to cell F5 and put in the following formula.

• Then, pull the Fill Handle icon.

This converts the negative number into a positive, and the positive number does not change.

6. How to Get Absolute Value in Excel Using Pivot Table

Pivot Table is an interesting feature of Excel. This feature can also be used how to get absolute value in Excel. Go to the below section for details.

📌 Steps:

• Select range B4:E9 >> Insert tab >> click drop-down of the PivotTable >> select From Table/Range.

• The PivotTable from table or range window appears.
• Select the Existing Worksheet option.
• Move to the Location section and select a cell (cell G4) in the dataset.
• Finally, press the OK button.

• The PivotTable1 has been inserted in the dataset. We can see the PivotTable Fields tab in the upper-right section.

• Now, customize the PivotTable1.
• Drag Brand into Rows and Variance into Values section.
• On the left side, we can see the customized Pivot Table.

• Go to the PivotTable Analyze tab >> click into Field, Items, & Sets option >> click on the Calculated Field option from the list.

• Insert Calculated Field window appears.
• Input a customized name in the Name section and insert the following formula in the Formula section.

• After that, click on the Add button and finally click on OK.

• Look at the Pivot Table of the dataset.

We get absolute values for the Variance column into the Sum of Absolute Variance column.

7. How to Get Absolute Value in Excel Using Power Query

The Power Query is another way to get absolute values for a large dataset in Excel. For details look at the below discussion.

📌 Steps:

• Select cell B4:E9 >> go to Data tab >> choose From Table/Range option.

• The Create Table window appears. Mark the My table has headers option.
• Then, press the OK button.

• We enter the Power Query Editor.
• Go to Add Column tab >> click on the Custom Column option from the General group.

• The Custom Column window appears. Insert a name in the New column name box.
• Insert the following formula in the Custom column formula box.

• Next, press the OK button.

• Look at the Power Query window.

We get the absolute values on the Absolute Variance column.

8. VBA Macro to Get Absolute Values for Large Excel Dataset

VBA code is an excellent way to perform a predetermined task repeatedly. According to this article, we can get absolute values for a large dataset using VBA macro. Look at the below section for details.

📌 Steps:

• Go to the Sheet Name section and press the right button of the mouse.
• Choose the View Code option from the Context Menu.

• We enter the VBA window.
• Choose the Module option from the Insert tab.
• Then, paste the following VBA code on the Module.

Sub Calculating_Absolute_Value()
Dim Variance, V_cell As Range
Set Variance = Application.InputBox(Prompt:="Select Input Range", Type:=8)
    For Each V_cell In Variance
        V_cell.Offset(0, 1).Value = Abs(V_cell.Value)
    Next V_cell
End Sub

Code Breakdown

• Dim Variance, V_cell As Range

Declares two variables of the range type.

• Set Variance = Application.InputBox(Prompt:=”Select Input Range”, Type:=8)

Set the value of Variance using an InputBox of the range type.

• For Each V_cell In Variance

              V_cell.Offset(0, 1).Value = Abs(V_cell.Value)

         Next V_cell

Apply a loop for each cell of Variance. Abs function determines the absolute value for V_cell and inserts that value into the next column of the same row. Then, move to the next cell of V_cell.

• Run the VBA code by pressing the F5 button.
• Select the desired range (E5:E9) in the Input window.
• Then, press the OK button.

• Look at the dataset.

We can see the absolute values are on the Absolute Variance column.

How to Get Absolute Value in Excel: 5 More Examples

The ABS function is widely used to get absolute value in Excel. Here, we will see some practical examples based on the ABS function to calculate absolute values in Excel.

1. Calculate the Absolute Value for the Conditional Sum

In this example, we find out the absolute value of the conditional sum. We have two series of data in columns B and C. We want to get the sum of values small than 0 from Series 1 and the sum of values greater than 0 from Series 2.

For that, we applied the following formula based on the combination of ABS and SUMIF functions on cell F4 to get the absolute value for the conditional sum.

Formula Breakdown

• SUMIF(B5:B9,”<0″)

Determines the sum of values lower than 0 from Series 1.

Result: -55

• SUMIF(C5:C9,”>0″)

Determines the sum of values greater than 0 from Series 2.

Result: 50

• ABS(SUMIF(B5:B9,”<0″)+SUMIF(C5:C9,”>0″))

The ABS function determines the absolute value of the sum of the conditional sum of previous SUMIF functions.

Result: 5

Read More: Changing Negative Numbers to Positive in Excel

2. Calculate the Absolute Sum Value for the Array Formula

In this example, we want to get the absolute value for an array sum. We can use the combination of SUM and ABS functions. The ABS function determines the absolute value of the array, and the SUM function adds the absolute values.

• Insert the following formula on cell E11 to get the absolute array sum of Variance.

We can also use the below formula based on the combination of SUMPRODUCT and ABS functions.

Read More: How to Sum Absolute Value in Excel

3. Find the Maximum/Minimum Absolute Value

In this example, we will find out the maximum and minimum absolute values in Excel. The combination of MAX and ABS is used for maximum and MIN and ABS for minimum absolute value.

We insert the following two formulas for maximum and minimum values at cells E11 and E12 respectively.

For maximum value:

For minimum value:

4. Average Absolute Values in Excel

Here, we will show how to get the average of absolute values. We have to insert the AVERAGE function with the ABS function for that.

• Insert the formula on cell E11 to get the average.

5. Calculating Tolerance of Variance

In this example, we will show the tolerance for the absolute value of variance using conditional formatting.

📌 Steps:

• First, we will find out the tolerance of variance using the following formula on cell F5.

• Then, drag Fill Handle icon downwards.

We can see if the variance is below the tolerance level showing 1, otherwise 0.

• Select range F5:F9 >> go to the Conditional Formatting >> choose Icon Sets >> choose the desired symbol from Indicators.

• Finally, we get the dataset with marking.

Right for a variance under tolerance level and cross for above tolerance level.

Read More: How to Calculate Absolute Difference between Two Numbers in Excel

Frequently Asked Questions

1. Can I get the absolute value of a complex number in Excel?

Ans: Yes, we can use the ABS function to get the absolute value of a complex number. The formula may look like this, ABS(x). Here, x is a complex number.

2. Can I use the ABS function to calculate the distance between two numbers on a number line?

Ans: We can use the ABS function to get calculate the distance between two numbers on a number. The distance between two numbers in the number line is the absolute value of the difference between two numbers. You can use the following formula: ABS(A1-A2). Here, A1, and A2 contain any two numbers.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Conclusion

In this article, we showed 8 different ways how to get absolute value in Excel. We also added some practical examples to get absolute values in Excel. I hope this will satisfy your needs. Don’t forget to give your suggestions in the comment box.

Related Articles

• Calculate Absolute Percentage Error with Excel Function
• How to Make All Numbers Positive in Excel

<< Go Back to Excel ABS Function | Excel Functions | Learn Excel