How To Get Absolute Value in Google Sheets
One mathematical concept that many people have difficulty in understanding is the notion of “absolute value”. Fortunately, absolute value is actually a pretty simple concept. Absolute value is the distance between the number and zero. Since distance can’t be negative, absolute value is always a positive number – so the absolute value of 5 is 5 and the absolute value of -5 is also 5. What if you want to use absolute values in your Google Sheets spreadsheets? Well then you’re in luck because there are actually three simple ways to accomplish this task. In this article I will give you a brief tutorial on all three methods of obtaining absolute value in Google Sheets.
The ABS Function
You can always just manually convert negative numbers to positive ones, and that would work fine if you were just trying to get an absolute value for one or two cells. However, imagine having a large spreadsheet with a table column that included 350 negative numbers. Then you would need to manually edit 350 cells in the table, which might take a while! Luckily, Google Sheets includes an ABS function so that you can quickly get absolute values for minus numbers without editing their cells. It’s a basic function that you can enter with this syntax: =ABS(value).The ABS value can either be a cell reference or a number.
For some examples, open a blank spreadsheet in Google Sheets. Then enter the values ‘-454,’ ‘-250’ and -‘350‘ in the cells A2:A4 as shown in the snapshot directly below. Now you can convert that dummy data to absolute values.
Select cell B2 and enter the function ‘=ABS(A2)’ in the fx bar. B2 will return the absolute value 454 when you press Enter. That’s the absolute value of -454 entered in A2.
Copy the function into the other cells with the fill handle. Select B2, left-click the cell’s bottom right corner and drag the cursor over B3 and B4. Then release the left mouse button to copy the ABS function into those cells as shown in the screenshot below. (This Tech Junkie guide provides further details for utilizing the fill handle in Google Sheets.)
ABS also calculates absolute values for the results of calculations. For example, select B5, enter ‘=ABS(A2+A4)’ in the function bar and press Return. B5 will return the absolute value of 804. The SUM function would return -804, but as an absolute value the result is 804.
Add Absolute Values with SUMPRODUCT
ABS doesn’t add a range of numbers within a single cell reference. Furthermore, a cell range might include a mixture of positive and negative numbers. As such, a SUMPRODUCT and ABS formula is perhaps the best way to add a series of numbers together for an absolute value result.
Before you add the SUMPRODUCT formula to your spreadsheet, enter ‘200’ in cell A5 and ‘300’ in A6. Then enter the formula ‘=SUMPRODUCT(ABS(A2:A6))’ in cell B6, and press Return. B6 now adds the cell range A2:A6 and returns an absolute value of 1,554.
You can also expand the formula so that it adds two, or more, cell ranges. Select cell B7 in your Sheets spreadsheet, and input ‘=SUMPRODUCT(ABS(A2:A6))+SUMPRODUCT(ABS(B2:B4))’ in the function bar. The formula will add the numbers in the ranges A2:A6 and B2:B4 with the absolute value total of 2,608.
Convert the Negative numbers to positive values
An option that converts negative numbers to positive alternatives would be handy addition to Sheets’ toolbar. With such an option you could quickly remove the minus (-) from lots of cells to add absolute values to a spreadsheet. Power Tools is a Sheets add-on with plenty of tools, and it also includes an option that converts number signs. You can add Power Tools to Sheets from this website page.
After installing the add-on, open your Sheets spreadsheet and click Add-ons > Power Tools. Select Convert from the submenu to open Power Tools as in the shot below. That opens the add-on’s Convert options.
Now click the Convert number sign check box there. Select Convert negative numbers to positive from the drop-down menu. Select the cell range A2:A4 on your Sheets spreadsheet with the cursor. Press the Convert button on the add-on’s sidebar.
That removes the minus signs from the cells A2:A4 as in the snapshot directly below. Thus, those cells now include absolute values. With this conversion option you can quickly get the absolute values for a large range of cells without entering any ABS function in adjacent column.
So you can get absolute values in Sheets with either the ABS function or Power Tools without manually editing cells. This brief YouTube video provides further details about adding ABS to Sheets spreadsheets. You can also add the same ABS function to Excel spreadsheets as covered in this Tech Junkie tutorial. Got any other clever ways to derive absolute values in Sheets? Share them with us below!