SIGN function
SIGN shows the direction of a number, returning 1 for positive, -1 for negative and 0 for zero. It is handy for flagging increases and decreases in a column of changes.
Example
Read it as a sentence: check whether -9 is positive, negative, or zero, which gives -1 for a negative number.
A
B
C
D
1
-1
2
3
Syntax
SIGN(number)
-
number(required) - The value to test.
Returns: 1 for positive, -1 for negative, 0 for zero.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=SIGN(0.5) | 1 | Positive |
=SIGN(0) | 0 | Zero |
=SIGN(-0.5) | -1 | Negative |
=SIGN(2) | 1 | Larger value |
=SIGN("0.5") | 1 | Text number "0.5" |
=SIGN(TRUE) | 1 | TRUE |
=SIGN("abc") | #VALUE! | Non-numeric text |
=SIGN(Data!E4) | #DIV/0! | Error cell propagates |
The Data sheet these formulas read
A cell that starts with = holds that formula.
E
1
5
2
abc
3
TRUE
4
=1/0
Data
Watch out for
- Text returns #VALUE!.
Where it works
- Excel
- SIGN is in every version of Excel from 2007 on.
- Google Sheets
- Same name, same behaviour for everyday use.
Part of Math and trig functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .