This is an overview:

**The ABS Function**

**Function Objective:**

The **ABS **function is used to get the absolute value of a number. You will get only a positive number.

**Syntax:**

**=ABS(number)**

**Argument:**

ARGUMENTS |
REQUIRED/OPTIONAL |
EXPLANATION |
---|---|---|

number |
Required |
The species number for which we want to get the absolute value |

**Returns:**

A number with a positive sign.

The sample dataset showcases of a store’s profit in the 1st six months of 2021.

To get the absolute results in this dataset:

**Step 1:**

- Add a column:
**Absolute Value**.

**Step 2:**

- Enter the
**ABS**function in**D5**. Use**C5**as the argument. The formula is:

`=ABS(C5)`

**Step 3:**

- Press
**Enter**.

**Step 4:**

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

All objects are positive in the Result section. The **ABS **function affects the negative numbers only. It has no impact on positive numbers and zeros. It converts negative numbers into positive ones.

**Example 1 – Find the Absolute Variance Using the ABS Function**

**Step 1:**

- Enter the actual and expected revenue:

**Step 2:**

To see the difference between the actual and expected revenue in the **Error **column:

- Enter the formula in the
**Error**column. Drag down the Fill Handle to see the result in the rest of the cells.

`=D5-C5`

This difference is the variance. There both positive and negative values.

Use the **ABS **function to see the absolute variance:

**Step 3:**

- Enter the
**ABS**function in the**Error**column:

`=ABS(D5-C5)`

**Step 4:**

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

The absolute variance is displayed.

**Example 2 – ****Get the Absolute Variance with a Condition using the ABS Function**

**Step 1:**

- Add a column (
**Result**) to see the conditional variance.

**Step 2:**

- Enter the formula in
**E5**:

`=SUMPRODUCT(--(ABS(D5-C5)>100))`

A condition is set: **1 **for a variance value greater than 100. Otherwise, **0**.

**Step 3:**

- Press
**Enter**.

**Step 4:**

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

This is the output.

**Example 3 – Find the ****Square Root of a Negative Number using the ABS Function**

**Step 1:**

- This is the sample dataset.

**Step 2:**

- Enter the
**SQRT**formula in**C5**:

`=SQRT(B5)`

**Step 3:**

- Press
**Enter**and drag down the**Fill Handle**.

The **SQRT** function displays errors for the negative numbers.

**Step 4:**

- Use the
**ABS**function:

`=SQRT(ABS(B5))`

**Step 5:**

- Press
**Enter**and drag down the**Fill Handle**.

The square root is displayed, including the negative values.

**Example 4 – Using the ****ABS Function to Find the Tolerance in Excel**

**Step 1:**

- Create a column to display the result.

**Step 2:**

- Enter the formula in
**E5**:

`=IF(ABS(D6-C6)<=100,"OK","Fail")`

- Tolerance was set to
**100**.

**Step 3:**

- Press
**Enter**.

**Step 4:**

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

Cells below the tolerance level show **OK**. Otherwise, **Fail**.

**Example 5 – ****SUM Numbers Ignoring Their Signs with the ABS Function**

**Step 1:**

- Find the sum of these random numbers:

**Step 2:**

- Enter the formula in
**B12**:

`=SUM(ABS(B5:B10))`

**Step 3:**

- Press
**Ctrl+Shift+Enter**, as this is an array formula.

The total is displayed without signs.

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

**Example 6 – ****Return an Absolute Value of Negative Numbers and Identify the Non-negative numbers**

**Step 1:**

- Insert a column to see the result.

**Step 2:**

- Enter the formula in
**C5**:

`=IF(B5<0,ABS(B5),"Positive")`

**Step 3:**

- Press
**Enter**.

**Step 4:**

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

You get the absolute value for negative numbers. For non-negative numbers **Positive **is displayed.

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

**Example 7 – ****SUM the Negative Numbers Only with the ABS Function in Excel**

**Step 1:**

- To sum the negative numbers in the data below:

**Step 2:**

- Enter the formula in
**C12**:

`=SUM(IF(C5:C10<0,ABS(C5:C10),0))`

**Step 3:**

- Press
**Enter**.

This is the output.

**Example 8 – ****Get the Average of Absolute Values using the Excel ABS Function**

**Step 1:**

- To find the average profit in the dataset below:

**Step 2:**

- Enter the formula in
**C12**:

`=AVERAGE(ABS(C5:C10))`

**Step 3:**

- Press
**Ctrl+Shift+Enter**.

This is the output.

**Example 9 – **Find the Maximum/Minimum Absolute Value in Excel

- The dataset showcases temperatures in different states. To find the maximum absolute temperature, use:

`=MAX(ABS(C5:C10))`

- To find the minimum absolute value, use:

`=MIN(ABS(C5:C10))`

**Example 10 – **Calculate the Closest Even Number of Given Numbers

- To calculate the closest even number, use the following formula:

`=IF(ABS(EVEN(C5)-C5)>1,IF(C5 < 0, EVEN(C5)+2,EVEN(C5)-2),EVEN(C5))`

**Example 11 – **Identify the Closest Value from a List of Values in Excel

To identify the closest value to a specific value from a given list, use:

`=INDEX(C5:C10,MATCH(MIN(ABS(F4-C5:C10)),ABS(F4-C5:C10),0))`

**Example 12 – ****Calculate the Absolute Value Using the ABS Function in VBA**

**Step 1:**

- Go to the
**Developer**tab. - Select
**Record Macros**.

**Step 2:**

- Set
**Absolute**as the**Macro name**. - Click
**OK**.

**Step 3:**

- Enter the
**VBA**code.

```
Sub Absolute()
Rng = Selection
XML = ""
For Each i In Rng:
n = Abs(i)
X = X + Str(n) + vbNewLine + vbNewLine
Next i
MsgBox X
End Sub
```

**Step 4:**

- Select the cells.

**Step 5:**

- Press
**F5.**

The selected range is **C5:C8**.

**Things to Remember**

- In an array function, press
**Ctrl+Shift+Enter**instead of**Enter**. - Only numeric values can be used with this function.

## Frequently Asked Questions

**1. Can the ABS function be nested within other functions?**

Yes, you can use it as part of a larger formula.

**2. How does the ABS function handle zero?**

The **ABS **function treats zero as a non-negative number, so **ABS(0) **will return **0**.

**Download Practice Workbook**

Download the practice workbook.

## ABS Function in Excel: Knowledge Hub

**How to Get Absolute Value in Excel****How to Make All Numbers Positive in Excel****How to Calculate Absolute Difference between Two Numbers in Excel****Opposite of ABS Function in Excel****Calculate Absolute Percentage Error with Excel Function**

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