How to Use ABS Function in Excel (12 Suitable Examples)

Get FREE Advanced Excel Exercises with Solutions!

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.

Here you will get some relevant articles to use ABS function to get absolute value, change all or negative numbers positive, sum absolute values and calculate absolute percentage error. You will also get an article to find opposite of ABS function in 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: 12 Suitable Examples

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.


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


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.


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.


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 the total without any concern about their signs.

Read More: How to Sum Absolute Value in Excel


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 show Positive.

Read More: Changing Negative Numbers to Positive in Excel


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 functions 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. Find Maximum/Minimum Absolute Value in Excel

Here, we have a dataset that contains the temperatures of some states. To find maximum absolute temperature we will use the MAX and ABS functions.

=MAX(ABS(C5:C10))

To find minimum absolute value use the MIN and ABS functions like below.

=MIN(ABS(C5:C10))

Using MAX and MIN Functions to Find Maximum and Minimum Absolute Value


10. Calculate Closest Even Number of Given Numbers

To calculate the closest even number of the given numbers you can use the IF, EVEN and ABS functions in Excel. We’ll use the following formula to find the closest even temperature.

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

Calculating the Closest Even Number of Given Numbers


11. Identify Closest Value from a List of Values in Excel

Suppose you want to identify the closest value of a specific value from a given list. To do that, use INDEX, MATCH, MIN and ABS functions like the following formula.

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

Identifying Closest Value from a List of Values in Excel


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

Frequently Asked Questions

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

Yes, the ABS function can be nested within other functions in Excel. You can use it as part of a larger formula to manipulate numeric values and perform calculations.

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 this practice workbook to exercise while you are reading this article.


Conclusion

In this article, we show how to use the ABS function in Excel with easy examples. Here, we also included relevant articles where you find other practical examples of this function for different purposes. I hope this will satisfy your needs. Please give your suggestions and feedback in the comment box.


ABS Function in Excel: Knowledge Hub


<< Go Back to Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Alok Paul
Alok Paul

Alok Paul has completed his B.Sc. in Electronics and Telecommunication Engineering from East West University. He has been working on the ExcelDemy project for more than 2 years. He has written 220+ articles and replied to numerous comments. He is experienced in Microsoft Office, especially in Excel. He also led some teams on Excel and VBA content development. He has a keen interest in Advanced Excel, Data analysis, Excel Pivot Table, Charts, and Dashboard. He loves to research... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo