# How to Use ABS Function in Excel (9 Suitable Examples)

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.

## 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.

## Related Articles #### Alok

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts 