Purpose
Return value
Syntax
=SIGN(number)
- number - The number to get the sign of.
Using the SIGN function
The SIGN function returns the sign of a number as +1, -1 or 0. If number is positive, SIGN returns 1. If number is negative, sign returns -1. If number is zero, SIGN returns 0.
The SIGN function takes one argument, number , which must be a numeric value. If number is not numeric, SIGN returns a #VALUE! error.
Examples
=SIGN(5) // returns 1
=SIGN(-3) // returns -1
=SIGN(0) // returns 0
SIGN can be used to change negative numbers into positive values like this. For example, with -3 in cell A1, the formula below returns 3:
=A1*SIGN(A1)
=-3*-1
=3
The formula above has no effect on positive numbers, since SIGN will return 1. However, the ABS function provides a simpler solution:
=ABS(A1) // absolute value of A1
Notes
- If number is in the range (-∞,0) SIGN(number) will return -1.
- If number is equal to 0 SIGN(number) will return 0.
- If number is in the range (0,∞)(number) SIGN will return 1.
Purpose
Return value
Syntax
=SQRT(number)
- number - The number to get the square root of.
Using the SQRT function
The Excel SQRT function returns the square root of a positive number. SQRT returns an error if number is negative.
The SQRT function takes one argument, number , which must be a numeric value. If number is not numeric, SQRT returns a #VALUE! error. If number is negative, SQRT returns a #NUM! error.
Examples
=SQRT(9) // returns 3
=SQRT(81) // returns 9
=SQRT(144) // returns 12
=SQRT(0.25) // returns 0.5
=SQRT(0) // returns 0
Negative numbers
The SQRT function will return a #NUM! error when number is negative:
=SQRT(-9) // returns #NUM!
To get the square root of a negative number (as if the number was positive), wrap the number in the ABS function like this:
=SQRT(ABS(-9)) // returns 3
Notes
- If number is not numeric, SQRT returns a #VALUE! error.
- If number is negative, SQRT returns a #NUM! error.
