The** ABS** function is one of the default functions of Microsoft Excel. When we work with data to find out the difference, it is natural to get a negative value. But this negative value does not present the result exactly how we want to see it. In that case, we use this **ABS **function. In this article, we will discuss how to use the Excel **ABS **function.

The above image is an overview of this article, which is representing the applications of the **ABS **function. You will get a detailed knowledge of the **ABS **function throughout this article.

**Table of Contents**hide

**Download Practice Workbook**

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

**Introduction to ABS Function**

**Function Objective:**

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

**Syntax:**

**=ABS(number)**

**Argument:**

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

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

**Returns:**

In return, we will get a number with a positive sign.

**Available in:**

Excel for Microsoft 365, Microsoft 365 for Mac, Excel for the web Excel 2021, Excel 2021 for Mac, Excel 2019, Excel 2019 for Mac, Excel 2016, Excel 2016 for Mac, Excel 2013, Excel 2010, Excel 2007, Excel for Mac 2011, Excel Starter 2010.

**How to Use the ABS Function**

Here, we will show how to use the **ABS **function. For this, we take the data of a superstore’s profit for the 1st six months of 2021.

We will show how to use the **ABS **function to get the absolute results from our dataset.

**Step 1:**

- We will add a column named
**Absolute Value**in the data set.

**Step 2:**

- Write the
**ABS**function on**Cell D5**. - Use
**C5**as the argument. So, the formula will be:

`=ABS(C5)`

**Step 3:**

- Then press
**Enter**.

**Step 4:**

- Pull the
**Fill Handle**icon to**Cell D10**.

Now, we can see that all the objects are positive in the Result section. This **ABS **function affects only the negative numbers. It has no impact on positive numbers and zeros. It converts the negative numbers into positive ones.

**9 Examples of ABS Function in Excel**

We will show the **ABS **function with different examples so that you can use this function when necessary.

**1. Find Absolute Variance Using ABS Function**

Here, we will an example of the **ABS **function to show absolute variance.

**Step 1:**

- We show the data of revenue that is actual and expect here.

**Step 2:**

- Now, show the difference of actual and expected revenue in the
**Error**column. - We put a formula in the
**Error**column and pull the**Fill Handel**icon. The formula is:

`=D5-C5`

This difference is variance. We get variance values both positive and negative. Now, we will use the **ABS **function to show the absolute variance.

**Step 3:**

- Insert the
**ABS**function in the**Error**column. - So, the formula will be:

`=ABS(D5-C5)`

**Step 4:**

- Now, drag the
**Fill Handel**icon.

Now, we can see the absolute variance.

**2. Get Absolute Variance with Condition with ABS Function**

In the previous example, we showed absolute variance. Now, we will try to find out absolute variance with conditions using this ABS function. We will insert the **SUMPRODUCT **function with the **ABS **function.

**Step 1:**

- We add a column
**Result**to get the conditional variance.

**Step 2:**

- Now, write the formula on
**Cell E5**. The formula is:

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

Here, we set a condition that we will get **1 **for the variance value greater than 100. Otherwise, we will get **0**.

**Step 3:**

- Then press
**Enter**.

**Step 4:**

- Now, pull the
**Fill Handle**icon.

Here, we can see the result is **1 **for variance over 100 and **0 **for the rest.

**3. Square Root of a Negative Number by ABS Function**

We can find the square root of any number using the **SQRT **function. But if the number is negative it will result in an error. Here, we will use the **ABS **function to get the square root of any negative number.

**Step 1:**

- To show this example we took a set of random data.

**Step 2:**

- Now, apply the
**SQRT**function on**Cell C5**. So, the formula is:

`=SQRT(B5)`

**Step 3:**

- Now, press
**Enter**and pull the**Fill Handle**icon.

Here, we can see that the **SQRT** function is working for the positive numbers and zeros. But error showing for the negative numbers.

**Step 4:**

- Now, insert the
**ABS**function. So, the formula becomes:

`=SQRT(ABS(B5))`

**Step 5:**

- Again, press
**Enter**and pull the**Fill Handle**icon.

Now, we get the square root result for all the values including the negative values.

**4. ABS Function to Find Tolerance in Excel**

Here, we will show the example of tolerance using the **ABS **function. We need to take the help of **the IF function** in this example.

**Step 1:**

- Here, we will show cells with tolerance.

**Step 2:**

- Write the formula in
**Cell E5**. The formula is:

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

- We set a tolerance value of
**100**.

**Step 3:**

- Then press
**Enter**.

**Step 4:**

- Now, pull the
**Fill Handle**icon.

When cells are below tolerance level show **OK** and otherwise **Fail**.

**5. SUM Numbers Ignoring Their Signs with ABS Function**

In this example, we will sum some numbers by ignoring their signs. This will be an array formula.

**Step 1:**

- We will find the sum of the below random numbers.

**Step 2:**

- Go to
**Cell B12**and write down the formula.

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

**Step 3:**

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

Now, we can see that we get total without any concern about their signs.

**6. Return Absolute Value of Negative Numbers and Identify Non-negative**

In this example, we will show how to identify non-negative numbers. And if the number is negative, we will get a positive number in return.

**Step 1:**

- We will identify positive numbers from the below data.

**Step 2:**

- Write the formula on
**Cell C5**. The formula is:

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

**Step 3:**

- Then, press
**Enter**.

**Step 4:**

- Now, pull the
**Fill Handle**icon to the last containing data.

Here, we get the absolute value for the negative numbers. And for non-negative numbers shows **Positive**.

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

In this example, we will show how to sum all the negative numbers only. We will take the help of **SUM **and **IF **function here.

**Step 1:**

- We will sum the negative numbers or the losses from the below data.

**Step 2:**

- Go to the
**Cell C12**. - Write the formula:

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

**Step 3:**

- Now, press
**Enter**.

This example shows the sum of the negative numbers only.

**8. Get Average Absolute Values by Applying Excel ABS Function**

We will show to find average using **ABS **function. We will use the average function here.

**Step 1:**

- We will find the average profit of the below data.

**Step 2:**

- Write the formula on
**Cell C12**:

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

**Step 3:**

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

Here, we get the average with the **AVERAGE **and **ABS **functions.

**9. Calculate Absolute Value Using ABS Function in VBA Macros**

We will apply the **ABS **function in **VBA Macros**.

**Step 1:**

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

**Step 2:**

- Set
**Absolute**as the**Macro name**. - Then press
**OK**.

**Step 3:**

- Now, write the below
**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:**

- Now, select cells desired excel sheet.

**Step 5:**

- The press
**F5**in the**VBA**command module

Here, we selected range **C5:C8** and the result is showing.

**Things to Remember**

- In the array function please in mind to press
**Ctrl+Shift+Enter**instead of**Enter**. - Only numeric values can be used with this function. For alphabetic values will get error results.

**Conclusion**

In this article, we show how to use the ABS function in Excel with easy examples. I hope this will satisfy your needs. Please have a look at our website **Exceldemy.com** and give your suggestions in the comment box.