How to get absolute value in Excel: ABS function with formula examples

Convert negative numbers to positive numbers

In situations when you need to change negative number to positive numbers, the Excel ABS function is an easy solution.

Supposing, you calculate the difference between two numbers by subtracting one number from the other. The problem is that some of the results are negative numbers while you want the difference to always be a positive number:

Wrap the formula in the ABS function:

=ABS(A2-B2)

And have the negative numbers converted to positive, leaving the positive numbers unaffected:

Find if value is within tolerance

Another common application of the ABS function in Excel is to find whether a given value (number or percentage) is within expected tolerance or not.

With the actual value in A2, expected value in B2, and the tolerance in C2, you build the formula in this way:

• Subtract the expected value from the actual value (or the other way round) and get the absolute value of the difference: ABS(A2-B2)
• Check if the absolute value is less than or equal to the allowed tolerance: ABS(A2-B2)<=C2
• Use the IF statement to return the desired messages. In this example, we return "Yes" if the difference is within tolerance, "No" otherwise:

=IF(ABS(A2-B2)<=C2, "Yes", "No")

How to sum absolute values in Excel

To get an absolute sum of all numbers in a range, use one of the following formulas:

Array formula:

Regular formula:

In the first case, you use an array formula to force the SUM function to add up all the numbers in the specified range. SUMPRODUCT is an array type function by nature and can handle a range without extra manipulations.

With the numbers to be summed in cells A2:B5, either of the following formulas will work a treat:

Array formula, completed by pressing Ctrl + Shift + Enter:

=SUM(ABS(A2:B5))

Regular formula, completed with a usual Enter keystroke:

=SUMPRODUCT(ABS(A2:B5))

As shown in the screenshot below, both formulas sum absolute values of positive and negative numbers, ignoring the sign:

How to find the maximum/minimum absolute value

The easiest way to get the minimum and maximum absolute value in Excel is to use the following array formulas.

Maximum absolute value:

Minimum absolute value:

With our sample dataset in A2:B5, the formulas take the following shape:

To get the max absolute value:

=MAX(ABS(A2:B5))

To find the min absolute value:

=MIN(ABS(A2:B5))

Please be sure to properly complete the array formulas by pressing Ctrl+Shift+Enter.

If you don't like using array formulas in your worksheets, you can trick the ABS function into processing a range by nesting it into the array argument of the INDEX function like shown below.

To get the maximum absolute value:

=MAX(INDEX(ABS(A2:B5),0,0))

To get the minimum absolute value:

=MIN(INDEX(ABS(A2:B5),0,0))

This works because an INDEX formula with the row_num and column_num arguments set to 0 or omitted tells Excel to return a whole array rather than an individua value.

How to average absolute values in Excel

The formulas we used to calculate the min/max absolute value can average absolute values too. You will just have to replace MAX/MIN with the AVERAGE function:

Array formula:

=MAX(ABS(range))

Regular formula:

=AVERAGE(INDEX(ABS(range),0,0))

For our sample data set, the formulas would go as follows:

Array formula to average absolute values (entered by pressing Ctrl + Shift + Enter):

=MAX(ABS(A2:B5))

Regular formula to average absolute values:

=AVERAGE(INDEX(ABS(A2:B5),0,0))