How to Use the ABS Function in Excel

This guide will explain how to use the ABS function in Excel.

In mathematics, the absolute value of a number is its actual distance from zero or the origin point. When finding the absolute value of a number, we disregard the negative sign of negative numbers while positive numbers are unaffected.

The ABS function in Excel allows users to find the absolute value of any number. The function takes a real number and outputs the same value without its sign.

In this guide, we will provide a step-by-step tutorial on how to use the ABS function in a variety of use cases in Excel.

The Anatomy of the ABS Function

The syntax of the ABS function is as follows:

=ABS(number)

Let’s look at each argument to understand how to use the ABS function.

• = the equal sign is how we start any function in Excel.
• ABS() refers to the built-in ABS function. The function removes the negative sign from negative values and leaves positive numbers unaffected.
• The number function must be a real number that you want to convert into its absolute value.
• The number argument could be the value itself or a cell reference to another cell. It can also be the output of another function or series of operations.

A Real Example of Using the ABS Function in Excel

Let’s explore a few simple examples where we can use the ABS function to calculate the absolute value of a number.

Using ABS Function to Find Absolute Difference

When subtracting numbers, the difference will either be positive or negative depending on whether the minuend (the number to subtract from) or subtrahend (number to subtract) is larger. We can use the ABS function to find the absolute difference of two numbers instead.

In the table above, we have a dataset of prices of different items. Each item has two prices, one from Store A and another from Store B. We want to set up another column that calculates the absolute difference in the prices between the two stores.

To find the absolute difference, we can use the following formula:

=ABS(A2-B2)

In the formula above, we placed our subtraction operation (A2-B2) as an argument for the ABS function. This ensures that no matter the result of our subtraction, the final result will be positive.

In the example above, we find out that the absolute price difference of the first pair of item prices is $5.00.

Using ABS to Find the Percentage Difference

The percentage difference between a measurement and its expected value can help assess the accuracy or precision of the measurement. The percentage difference can be understood as an absolute value since we are only interested in how far the actual measurement is from the expected value.

After identifying the percentage difference, we can then compare this value with some tolerance value (such as 10%). A percentage difference larger than our tolerance value can indicate something is defective with our measurement or approach.

Let’s try using the ABS function to identify the percentage difference of an actual measurement and the expected result. We can use the following formula to calculate the percentage difference:

=ABS((A2-B2)/B2)

The formula above finds the difference between the measured value and the expected value and divides it by the expected value. The equation is then wrapped with the ABS function since we’ll need the absolute value of the result.

We can see that the measurement in row 4 has a percentage difference that exceeds our tolerance value of 10%.

Click on the link below to create your own copy of our examples.

Head to the next section to read our step-by-step tutorial on how to use the ABS function in Excel.

How to Use the ABS Function in Excel

1. Select a blank cell where you want to output the result of the ABS function.
   
   In this example, we want to find the absolute value of the prices in columns A and B. We’ll start by outputting the absolute price difference of cells A2 and B2.
   
2. Type “=ABS(“ to start the ABS function.
   Next, enter the value, cell reference, or equation you want to convert into an absolute value.
   
   In the example above, we’ll input the formula A2-B2 into the ABS function.
   
3. Hit the Enter key to evaluate the ABS function.
   
   In our example above, we’ve determined that the absolute difference between our two prices is $5.00.
   
4. We can use the Fill Handle tool to find the price difference of the remaining items in our table.
   

These are all the steps you need to follow to start using the ABS function in Excel.

FAQs

1. How does the ABS function handle zero?
   The ABS function considers the number 0 as a non-negative number and simply returns 0.
2. Does the ABS function work with non-numeric values?
   No, the ABS function in Excel works only with numeric values. If you attempt to input a non-numeric value, such as text or an empty cell, the ABS function will return a #VALUE! error.

To learn more about using Excel function with the ABS function, you can read our post on how to find the median absolute deviation in Excel.

That’s all for this guide! Be sure to check out our library of spreadsheet resources, tips, and tricks!