How to get absolute value in Excel 2021

Excel is a program that has multiple functionalities, useful for different calculations, among other things. One of them is obtaining the absolute value in excel. The absolute value of a number is its value regardless of its sign. For this reason, regardless of whether the number is negative, it will always be positive. For example, the absolute value of -4 is 4.

This function is known in Excel as ABS and thanks to it, it is possible to obtain the absolute value of a number in a cell. It is a basic function that does not add absolute values. The syntax for ABS is: ABS (number).

ABS function for absolute value in Excel

ABS can be used with other Excel functions to increase your analytical skills, For example, we can have a series of both negative and positive numbers and perform the sum of all, the result in normal conditions should separate the positive numbers from the negative ones and show the difference between them, while, if we combine the SUMPRODUCT function with ABS this will add all the numbers as if they were all positive.

First step: number to get absolute value from

Now the first step will be to choose an ABS syntax. The syntax to use is basically the number of which we will obtain the absolute value.

To represent the use of ABS in cell A1 we enter a negative number and in cell B1 we will use the ABS formula and we will see that its result will always be positive:

How to get absolute value in Excel

As an example, open a blank Excel worksheet and enter -3454 in cell B3. Then select cell B4 and press the button fx to open the Insert Function window. Please select All from the drop-down menu OR select a category and click ABS to open the snapshot window directly below.

Step 2: add ABS function

Now press the cell reference button for the Number field and select B3. Press the button okay to add the ABS function to the spreadsheet. Cell B4 will return a value of 3454 as shown below.

ABS Excel

You can find the absolute value for a range of cells with this function by adding an ABS column to the worksheet. Then insert the ABS function into the cells of the column. Enter a = SUM function in a cell at the bottom of the column to sum the absolute values.

Other functions to get the absolute value in Excel

also can combine ABS with other functions to calculate the absolute value of positive numbers and negatives in Excel spreadsheets. SUMPRODUCT is one of the functions that ABS can include to give you the absolute value of a range of positive and negative values.

SUMPRODUCT function

First, enter some dummy data in the spreadsheet for the SUMPRODUCT function. Enter the values ​​-4, 4, and 7 in cells A2, A3, and A4. Select cell A5 and click inside the effect bar. Then enter the function = SUMPRODUCT (A2: A4) in the effect bar and press the Enter key. This will return 7 in cell A5, which is not the absolute value.

To find the absolute value of the data range, we need to incorporate ABS into the SUMPRODUCT function. Therefore, replace the original function = SUMPRODUCT (A2: A4) with = SUMPRODUCT (ABS (A2: A4)). So A5 will return 15 (4 + 4 + 7) for the cell range as shown directly below.

SUMIF function

Another way to get the absolute value in Excel is through the SUMIF function. The SUMIF function is a function with which you can sum values ​​that meet specific criteria. As such, you can also find the absolute value for a range of cells added together with SUMIF. The SUMIF syntax is: SUMIF (range, criteria,[sum_rango]).

You can find the absolute value of a range of cells by manually entering the SUMIF function in the effect bar. Select cell A6 and enter = SUMIF (A2: A4, »> 0 ″) – SUMIF (A2: A4,»

SUMIF

Array formulas in Excel

On the other hand, we also find Excel matrix formulas, which allow users to perform multiple calculations for a matrix (or column of values). Therefore, you can also add a SUM array formula to Excel that returns the absolute value of a series of numbers in a column or row. Ctrl + Shift + Enter is pressed to add matrix formulas to spreadsheets.

The formula of the SUM array for absolute values ​​is: = SUM (ABS (A2: A4)). Select cell A7 on your spreadsheet, and enter = SUM (ABS (A2: A4)) in the effect bar. However, don’t just press the Enter key. Instead, you must press Ctrl + Shift + Enter after entering the formula in the effect bar. Then the formula will have {} braces around it as shown in the image below. This array formula also returns 15 in A7, which is eThe absolute value for the data entered in cells A2: A4.

To end

In conclusion, there are few ways to find the absolute value of a range of numbers in Excel spreadsheets. Nevertheless, SUMIF, SUMPRODUCT, ABS and SUM array are the best functions and formulas to get absolute value. The complement Kutools for Excel also includes a tool Change sign of values which converts negative numbers in the worksheet to positive.

Other guides on Excel:

Scroll to Top