When working with numerical data, we often need to convert numbers to their absolute values. This is often useful in cases where we are working with distances or when we just want to make sure we have the magnitude of a value, irrespective of sign.
In this tutorial we will look at three ways to find the absolute value in Google Sheets for a given set of numbers:
- Using the ABS Function
- Converting all Negative Numbers to Positive
- Using Google App Script
Table of Contents
What Does Absolute Value Mean?
Absolute Value is the magnitude of a value, without considering its sign. It is a great way to make sure that a given quantity is non-negative.
For example, we can define the distance between two real numbers x and y as |x-y|. Finding the absolute value of this distance ensures we get just the distance between the two quantities, and this result does not differ with respect to which of the two quantities is larger.
Absolute values can also be useful when you want to find the total value of a set of negative and positive numbers. If you want just the total magnitude, the best way would be to find the sum of the absolute values of the numbers in the list.
How to Find Absolute Value in Google Sheets
Finding the absolute value of any given number simply involves removing the negative sign, if any. This is quite simple if you have a small set of values. You can simply remove the minus sign from each number manually.
However, it’s not so practical when you have a large number of values for which you need to find the absolute value. So, let us look at 4 easy ways to find the absolute values in Google Sheets. We are going to use the following dataset to demonstrate each method:
Note that the above list of numbers contains both positive and negative values.
Using the ABS Function to Find the Absolute Value in Google Sheets
The ABS function in Google Sheets is specifically used to find the absolute value of a number. The syntax for this function is quite simple:
ABS(value)
Here, value is the number for which you want to find the absolute value. This can be a number, a formula or a reference to a cell containing a number.
To find the absolute value for the list shown above, simply type the following formula in cell B2 and copy it down to the rest of the cells using the fill handle:
=ABS(A2)
Here’s the result that we get:
Alternatively, you can use the following formula in cell B2 and get the absolute values for all the values in the list in one go:
=ARRAYFORMULA(ABS(A2:A10))
Finding the Absolute Value in Google Sheets by Converting All Negative Numbers to Positive
The absolute value of a number is simply obtained by removing the minus sign (-) from all negative numbers.
In other words, it essentially involves converting all negative numbers to positive. There are a number of ways to get this done in Google Sheets.
One way is to use the IF function to check if the number is less than 0, and if it is, then multiply the number with -1. Otherwise, leave the number as it is.
This means to find the absolute value for our sample list, we need to type the following formula in cell B2 and copy it down to the rest of the cells using the fill handle:
=IF(A2<0,A2*-1,A2)
The result can be seen in the screenshot below:
Using Google Script to Find the Absolute Value in Google Sheets
This method uses a Google script or App Script to quickly convert a list of values to their absolute values. You can use the script given below to quickly convert a list of numbers to their absolute values:
function findAbsolute() {
 var app=SpreadsheetApp;
 var ss=app.getActiveSpreadsheet();
 var activeSheet=ss.getActiveSheet();
 var range=activeSheet.getActiveRange();
 var values=range.getValues();
 var value;
 for (var i in values){
  var row=values[i]
  for (var j in row) {
   value=row[j]
   if (value<0){
    row_num=parseInt(i)+1;
    col_num=parseInt(j)+1;
    console.log(value)
    console.log(row_num, col_num)
    activeSheet.getRange(row_num+1,col_num).setValue(value*(-1));
   }
  }
 }
}
Note: This script works on the original data, so this method works great if you don’t want to use a separate helper column to write the formula.
To use the above script, simply follow the steps given below:
- Navigate to Extensions->Apps Script from the main menu
- This opens the Apps Script window.
- Replace the code provided with the above code.
- Change the name of the script from ‘Untitled’ to ‘findAbsolute’.
- Save the macro by pressing the shortcut CTRL+S on your keyboard.
- Your script is now ready to run.
- Import this script into your worksheet as a macro by navigating to Extensions->Macros->Import macro.
- Select the name of the function, which is findAbsolute in this case, and press ‘Add Function’.
- Your script has now been added as a macro.
Now every time you need to convert a list of numbers to their absolute values, simply select the range of cells containing the values you want to convert and navigate to Extensions->Macros->findAbsolute.
The first time you try to run the script, you will be asked for authorization to run the script.
Click Continue and follow the instructions to authorize access to your spreadsheet. Once you’re done, your script will start running, and all your selected numbers will get converted to their absolute values.
Note: You could also add a button to your sheet and link it to your script, so that every time a user selects a range of cells and clicks on the button, the script runs and converts the selected values to their absolute values.
Absolute Value vs. Absolute Reference in Google Sheets
A lot of beginners get confused between the terms Absolute value and Absolute reference in Google Sheets, so it is important to understand the difference.
An absolute reference is a cell reference that is locked, making sure that the row and column numbers in the formula containing the cell reference won’t change when copied to another cell. This is different from a relative reference that adjusts the cell reference when the formula is copied to another cell.
To create an absolute reference in Google Sheets, we usually add a dollar sign before the row number and column letter; for example, the cell reference $A$2 is an absolute reference. You can also use the absolute reference shortcut, by pressing the F4 key on the keyboard.
In short, an absolute reference refers to the location of the cell containing a value. An absolute value, on the other hand, refers to the actual value in its pure form, without any negative or positive signs.
Conclusion
In this tutorial we showed you three ways to find the absolute value in Google Sheets.
The first two methods use formulas, so they work great when you want to get the absolute values in a separate column and retain the original values. The third method, on the other hand, uses a Google Sheets app script to convert the original set of values into their absolute values.
We hope you find this tutorial helpful and easy to follow.
