Absolute value in Google Sheets: how to use the ABS function

The absolute value of a real number is also known as its modulus, magnitude, or numeric value. It represents the number’s distance from zero on a number line without considering the sign. Since it represents a distance, the absolute value is always positive.

In this guide, you will learn what absolute value is, the symbol used to represent it, and how to find it. You will also learn how to use the ABS function in Google Sheets to find a number’s absolute value. Finally, you will learn how to use the ABS function in combination with other functions.

If you came here to lock a cell reference with dollar signs, such as $A$1, that is a different feature: see our guide to absolute cell references in Google Sheets.

What is absolute value?

The absolute value (modulus) of a real number corresponds to its non-negative value, regardless of its sign. It represents the distance from 0 on the number line in either direction. Therefore, the absolute value of -5 and 5 is the same: 5. In other words, the absolute value of a number is always positive.

What is the absolute value sign?

The absolute value of variable x is |x|, which is read as ‘mod x’ or ‘modulus of x’. If the variable in question has a value of -1, its modulus is represented by vertical bars on either side of it: |-1|. Therefore, |-1| represents the absolute value of minus one, which is equal to one: |-1| = 1.

How to calculate absolute value?

Let’s see how to find the absolute value of a number or expression.

Example: What is the absolute value of -2?

The absolute value of a positive number is the number itself, and the absolute value of a negative number is the number without the sign. Therefore, what is the absolute value of -2?

|-2| = 2

Example: What is the absolute value of (-2*4)+10?

While the first case is easy, if you’re dealing with an expression containing absolute values, be careful to apply them correctly. In other words, calculate the expression first, then find the absolute value of the result.

|(-2*4)+10| = |(-8)+10| = |2| = 2

However, what if the expression looked like this:

(|-2|*4)+10

In this case, the absolute value symbols only apply to the first term, not the entire expression. Therefore, the first step is to find the absolute value of that number. After that, you can perform the calculations.

(|-2|*4)+10 = (2*4)+10 = (8)+10 = 18

How to find absolute value in Google Sheets?

Now that you know what absolute value is, it’s time to learn how to find it in Google Sheets.

How to use the ABS function in Google Sheets

First, let’s get the absolute values of a set of values.

1. Open Google Sheets to the file containing your data.

2. In the cell where you want the absolute value, type in the ABS function.

3. After the opening parenthesis, select the cell containing the number whose absolute value you want. Close the parenthesis and press ‘Enter’.

4. That’s it. You have the absolute value of the first value in the set.

5. To get the absolute values of all the values in your set, grab the fill handle and drag it down to the last value.

Absolute value formula examples in Google Sheets

Follow the steps below to learn to use the ABS function in combination with other formulas and functions.

Sum absolute values in range

Let’s see how you can sum the absolute values in a range without having to find the absolute value for each one. To do this, you’ll need to use the ABS, SUM, and ARRAYFORMULA functions. To learn more about using formulas and functions in Google Sheets, check out our guide to 100+ functions and formulas for Google Sheets.

1. Open your spreadsheet in Google Sheets.

2. In an empty cell, type in the SUM function as shown below.

3. Right after the opening parenthesis, type in the ARRAYFORMULA function as below.

4. Finally, type in the ABS function after the parenthesis.

5. Select the range of cells containing the set of values. Remember that you’ll need three closing parentheses: one for each function. Press ‘Enter’ to see the result.

6. That’s it. You have the sum of the absolute values of every item in the set.

Find maximum & minimum absolute values in range

Follow the steps below to find the maximum and minimum absolute values in a range of cells. For this example, you will combine the ABS and ARRAYFORMULA functions with the MAX and MIN functions.

1. Open Google Sheets to the spreadsheet with your data.

2. To find the maximum absolute value, type in the MAX function in an empty cell.

3. Next, add the ARRAYFORMULA function after the opening parenthesis.

4. Finally, add the ABS function.

5. Select the range of cells with the set of values and remember to close the three sets of parentheses – one for each function.

6. Press ‘Enter’ to see the result.

7. To get the minimum absolute value, you can use the same formula as before and change MAX to MIN.

8. That’s it. You have the sum of the absolute values in the range, as well as the maximum and minimum absolute values.

Conclusion

As you have seen, the absolute value of a real number is its magnitude, regardless of its sign. As it represents the distance from zero, the absolute value or modulus of a number is always a non-negative value. However, when you’re dealing with absolute values, you are more likely to need to use them within more complex formulas rather than simply find the absolute value of numbers. Fortunately, this is easy to do using Google Sheets.

You now know what absolute value is, the symbol used to represent it, and how to calculate it. You also know how to find the absolute value of numbers in Google Sheets using the ABS function. Additionally, you have step-by-step instructions on how to use the ABS function in combination with others so that you can perform other operations with absolute values.

Microsoft Excel has the same ABS function, so =ABS(A2) returns the absolute value of cell A2 there as well.