This is an overview:

The sample dataset showcases the name of the **Brand**, and the present day, and the previous day sales.

To find the variance of sales the following formula was used in **E5**. **The Fill Handle icon** was dragged down to the rest of the cells.

`=D5-C5`

The output includes both positive and negative values in the **Variance** column.

**Method 1 – Using the Excel ABS Function to Get the Absolute Value**

- Go to
**F5**and enter the following formula.

`=ABS(E5)`

- Drag down the Fill Handle to see the result in the rest of the cells.

The **ABS **function returns the absolute value of the given reference.

**Read More:** **Opposite of ABS Function in Excel**

**Method 2 – ****Apply a Condition Using the IF Function to the Get Absolute Value**

- Go to
**F5**and enter the following formula. - Drag down the Fill Handle to see the result in the rest of the cells.

`=IF(E5<0,-E5,E5)`

The absolute value for variances is displayed in **column F**.

**Method 3 – ****Combining the SQRT and the POWER Functions to Find the Absolute Value**

- Go to
**F5**and enter the following formula. - Drag down the Fill Handle to see the result in the rest of the cells.

`=SQRT(POWER(E5,2))`

You can also use the **caret** (**^**)” operator instead of the **POWER **function:

`=SQRT(E5^2))`

**Method 4 – Using the ****Excel MAX Function to Get the Absolute Value by Comparing it to the Opposite Value**

- Go to
**F5**and enter the following formula. - Drag down the Fill Handle to see the result in the rest of the cells.

`=MAX(-1*E5,E5)`

**Method 5 – ****Using the SIGN Function to Multiply a Given Number by its Sign and get the Absolute Value**

- Go to
**F5**and enter the following formula.

`=SIGN(E5)*E5`

- Drag down the Fill Handle to see the result in the rest of the cells.

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

**Method 6 – ****How to Get the Absolute Value Using a Pivot Table**** in Excel **

**Steps:**

- Select
**B4:E9 >> Insert****>>****PivotTable****>>****From Table/Range**.

- In the
**PivotTable from table or range**window, select**Existing Worksheet**. - In
**Location**, select**G4**. - Click
**OK**.

**PivotTable1**is created.

- Drag
**Brand**into**Rows**and**Variance**into**Values**.

- Go to the
**PivotTable Analyze**tab**>>**click**Field, Items, & Sets****>>****Calculated Field**.

- In the
**Insert Calculated Field**window, enter a name in**Name**and use the following formula in**Formula**.

`=if(Variance<0,-1*Variance, Variance)`

- Click
**Add**. - Click
**OK**.

This is the output.

The absolute values of the **Variance **column are displayed in the **Sum of Absolute Variance** column.

**Method 7 – ****How to Get the Absolute Value Using the Power Query**** in Excel**

**Steps:**

- Select
**B4:E9 >>**go to**Data****>>****From Table/Range**.

- In the
**Create Table**window, check**My table has headers**. - Click
**OK**.

- In the
**Power Query Editor**, select**Add Column**. - Click
**Custom Column**in**General**.

- Enter a name in
**New column name**. - Use the following formula in
**Custom column formula**.

**=if [Variance] < 0 the -[Variance] else [Variance]**

- Click
**OK**.

This is the **Power Query **window:

It displays the absolute values in the **Absolute Variance** column.

**Method 8 – Using a ****VBA Macro to Get the Absolute Values for a Large Excel Dataset**

**Steps:**

- Go to the
**Sheet Name**and right-click. - Choose
**View Code**.

- In the
**VBA**window, choose**Module**in the**Insert**tab. - Enter the following
**VBA**code in 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)**

Sets 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**

Applies a loop to each cell of **Variance**. The ABS function determines the absolute value for the **V_cell** and inserts that value into the next column of the same row. It moves to the next cell of **V_cell**.

- Run the
**VBA**code by pressing**F5**. - Select
**E5:E9**in**Input**. - Click
**OK**.

This is the output:

The absolute values are displayed in the **Absolute Variance** column.

## How to Get the Absolute Value in Excel: 5 More Examples

### 1. Calculate the Absolute Value of a Conditional Sum

The dataset contains two data series in **columns B** and **C**. To get the sum of values smaller than **0** in **Series 1** and the sum of values greater than **0** in **Series 2:**

- Enter the formula in
**F4**:

`=ABS(SUMIF(B5:B9,"<0")+SUMIF(C5:C9,">0"))`

**Formula Breakdown**

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

Determines the sum of values smaller than **0** in **Series 1**.

**Result: -55**

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

Determines the sum of values greater than **0** in **Series 2**.

**Result: 50**

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

determines the absolute value of the sum returned by the **SUMIF **functions.

**Result: 5**

**Read More:** **Changing Negative Numbers to Positive in Excel**

### 2. Calculate the Absolute Sum Value in an Array Formula

- Enter the following formula in
**E11**to get the absolute sum of the**Variance**array.

`=SUM(ABS(E5:E9))`

You can also use the formula below:

`=SUMPRODUCT(ABS(E5:E9))`

**Read More:** **How to Sum Absolute Value in Excel**

### 3. Find the Maximum/Minimum Absolute Value

- Enter the following formulas to get the maximum and minimum values in
**E11**and**E12**.

**To get the maximum value:**

`=MAX(ABS(E5:E9))`

**To get the minimum value:**

`=MIN(ABS(E5:E9))`

### 4. Calculate the Average of Absolute Values in Excel

- Use the formula in
**E11**:

`=AVERAGE(ABS(E5:E9))`

### 5. Calculate the Tolerance of the Variance

**Steps:**

- Find the tolerance of the variance using the following formula in
**F5**.

`=(--(ABS(E5)<120))`

- Drag down the Fill Handle to see the result in the rest of the cells.

If the variance is below the tolerance level **1 **is displayed. Otherwise, **0**.

- Select
**F5:F9 >>**go to**Conditional Formatting****>>****Icon Sets >>**choose a symbol in**Indicators**.

This is the output.

**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, use the **ABS **function: **ABS(x)**. **x** is the complex number.

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

Ans: The distance between two numbers in a number line is the absolute value of the difference between two numbers. You can use the following formula: **ABS(A1-A2)**.

**Download Practice Workbook**

Download the practice workbook.

**Related Articles**

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