ABS

Excel Functions

Find the absolute value of a number

Syntax

EXCEL

=ABS(number)

Arguments

Parameter	Description
`number`	The number to get the absolute value of.

Return Value

A positive number.

Details

The ABS function returns the absolute value of a number. You can think about absolute value as a number's distance from zero on a number line. ABS converts negative numbers to positive numbers. Positive numbers and zero (0) are unaffected. The ABS function takes just one argument, number, which must be a numeric value. If number is not numeric, ABS returns a #VALUE! error. Negative numbers become positive, while positive numbers and zero (0) are unaffected: 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 numb

Examples

Basic example

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

EXCEL

=ABS(-3) // returns 3
=ABS(5) // returns 5
=ABS(0) // returns 0

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 calcul

EXCEL

=B1-A1 // negative or positive result

Absolute Variance

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

EXCEL

=ABS(B1-A1)  // ensure positive result

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 ab

EXCEL

=SUMPRODUCT(--(ABS(variances)>100))

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:

EXCEL

=SQRT(-4) // returns #NUM!

Square root of negative number

To handle a negative number like a positive number, you can use the ABS function like this:

EXCEL

=SQRT(ABS(A1))