The ABS function takes just one argument, number, which must be a numeric value. If number is not numeric, ABS returns a #VALUE! error.

Basic example

Negative numbers become positive, while positive numbers and zero (0) are unaffected:

Absolute Variance

Calculating the variance between two numbers is a common problem. For example, with a forecast value in A1 and an actual value in B1, you might calculate variance like this: When B1 is greater than A1, variance is a positive number. However, when A1 is greater than B1, the result will be negative. To ensure the result is a positive number, you can use ABS like this: See a detailed example here.

Counting absolute variances with conditions

The ABS function can be used together with the SUMPRODUCT function to count absolute variances that meet specific conditions. For example, to count absolute variances greater than 100, you can use a formula like this: This formula is explained in more detail here.

Square root of negative number

The SQRT function calculates the square root of a number.  If you give SQRT a negative number, it will return a #NUM! error: To handle a negative number like a positive number, you can use the ABS function like this:

Calculating tolerance

To calculate whether a value is within tolerance or not, you can use a formula like this: See a detailed explanation here.

Dave Bruns

Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.