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.

Use of ABS Function in Excel

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.


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.

Data set to Use Excel ABS Function

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)

Data set to Use Excel ABS Function

Step 3:

  • Then press Enter.

Step 4:

  • Pull the Fill Handle icon to Cell D10.

Data set to Use Excel ABS Function

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

Find Absolute Variance Using ABS Function

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)

Find Absolute Variance Using ABS Function

Step 4:

  •  Now, drag the Fill Handel icon.

Now, we can see the absolute variance.

Read More: 51 Mostly Used Math and Trig Functions in Excel


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))

Get Absolute Variance with Condition 9n Excel

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.

Get Absolute Variance with Condition 9n Excel

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

Read More: 44 Mathematical Functions in Excel (Download Free PDF)


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)

Square Root of a Negative Number by ABS Function

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))

Square Root of a Negative Number by ABS Function

Step 5:

  • Again, press Enter and pull the Fill Handle icon.

Square Root of a Negative Number by ABS Function

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

Read More: How to Use SQRT function in Excel (6 Suitable Examples)


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.

ABS Function to Find Tolerance

Step 3:

  • Then press Enter.

Step 4:

  • Now, pull the Fill Handle icon.

ABS Function to Find Tolerance

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))

SUM Numbers Ignoring Their Signs with ARRAY Formula in Excel

Step 3:

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

SUM Numbers Ignoring Their Signs with ARRAY Formula in Excel

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


Similar Readings


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")

Identify Negative value using ABS function

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.

Identify Negative value using ABS function

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))

Get Average Absolute Values Applying ABS Function

Step 3:

  • Press Ctrl+Shift+Enter.

Get Average Absolute Values Applying ABS Function

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.

Calculate Absolute Value Using ABS Function in VBA Macros

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

Calculate Absolute Value Using ABS Function in VBA Macros

Step 4:

  • Now, select cells desired excel sheet.

Step 5:

  • The press F5 in the VBA command module

Calculate Absolute Value Using ABS Function in VBA Macros

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

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

Leave a reply

ExcelDemy
Logo