Excel SIGN Function: Tell Positive From Negative
Returns 1 for a positive number, -1 for a negative one, and 0 for zero.
SIGN discards the size of a number and keeps only its direction. Anything above zero returns 1, anything below returns -1, and zero returns 0. It is the exact complement of ABS, which keeps the size and discards the direction.
Its everyday use is turning a column of changes into a column of labels. A nested IF asking whether the value is above zero, then whether it is below, takes three branches; SIGN collapses the same question into one number you can feed straight into CHOOSE or a lookup.
It also restores a sign after you have deliberately removed one. Taking a root or a logarithm requires a positive input, so the pattern SIGN(x) * f(ABS(x)) applies the function to the magnitude and puts the direction back afterwards.
Syntax
=SIGN(number)Arguments
numberRequired- The value to test. Only its sign matters; the magnitude is discarded.
The example data
Headers in row 1, data in A2:D5.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Item | On hand | Per box | Adjustment |
| 2 | Cordless drill | 47 | 12 | -8 |
| 3 | Extension lead | 140 | 24 | 15 |
| 4 | Safety goggles | 63 | 18 | 0 |
| 5 | Work gloves | 210 | 30 | -22 |
Worked examples
=SIGN(D2)Result: -1
The adjustment is negative, so SIGN reports the direction as -1.
=SIGN(D3)Result: 1
A positive adjustment of 15. The 15 itself is thrown away.
=SIGN(D4)Result: 0
Zero is its own case, which is what makes SIGN three-way rather than binary.
=CHOOSE(SIGN(D2) + 2, "Down", "No change", "Up")Result: Down
Adding 2 shifts -1/0/1 to 1/2/3, which CHOOSE can index directly. One formula instead of two nested IFs.
Now practise it
Reading about a formula is not the same as writing one. Open this function's exercise and type it into a real grid — you get instant feedback on exactly which cell is wrong and why.
Common errors and how to fix them
#VALUE!
Why it happens: The argument is text rather than a number.
How to fix it: Numbers stored as text need converting first — SIGN will not coerce them.
Returns 0 for a tiny negative number
Why it happens: Floating-point rounding can leave a calculated difference at something like -1E-15, which is genuinely below zero and reports as -1, not 0.
How to fix it: Round before taking the sign: =SIGN(ROUND(A2-B2, 10)).
CHOOSE index out of range
Why it happens: SIGN returns -1, which CHOOSE cannot use as an index.
How to fix it: Add 2 so the range becomes 1 to 3, as in the example above.
Tips worth knowing
- SIGN times ABS gives back the original number: they split it into direction and magnitude.
- =SIGN(A2) * POWER(ABS(A2), 1/3) is how you take a cube root of a possibly negative number.
- For a two-way label, IF is clearer. SIGN wins when zero needs its own answer.
- SIGN on a date returns 1, because dates are positive numbers underneath.
Frequently asked questions
What does SIGN return for zero?
Exactly 0, which is its own third case rather than being lumped in with positives. That three-way result is the main reason to use SIGN instead of a simple greater-than test.
How do I label a change as up, down or flat?
=CHOOSE(SIGN(A2) + 2, "Down", "No change", "Up"). The +2 turns -1, 0 and 1 into 1, 2 and 3, which are valid CHOOSE indexes.
What is the difference between SIGN and ABS?
They are complements. ABS keeps the magnitude and discards the sign; SIGN keeps the sign and discards the magnitude. Multiply the two together and you recover the original value.
Related functions
Guides that use it
Longer reads where this function does real work in a real sheet.