Excel like a finance pro.
← The libraryPractice · 2 questions →
Math function
SIGN
Returns 1 for positive, -1 for negative, and 0 for zero.
OccasionalDifficulty 1000 · CapableUsage rank #143 of 520
When to use it
Returns 1, 0, or -1 depending on whether a number is positive, zero, or negative. Useful for classifying variances and for pulling a sign out of a calculation to reapply later.
The shape of it
- Syntax
=SIGN(number)
Worked examples
Negative
=SIGN(-12) → -1
Negative.
Zero
=SIGN(0) → 0
Zero is its own case.
Positive
=SIGN(45) → 1
Positive.
Worth knowing
- =SIGN(actual-budget) feeds an icon set or a "Favorable/Unfavorable" SWITCH.
- =SIGN(x)*ROUNDDOWN(ABS(x),0) truncates toward zero; TRUNC does the same more simply.
- SIGN of a difference sorts more cleanly than the difference itself when you only care about direction.
Where it goes wrong
- Text arguments return #VALUE!.
- Tiny floating-point residues (like 1E-15) return 1 or -1 when you expected 0; ROUND first.
Related
Learn the moves here — or let Wauvel run them on your numbers.
Meet your AI CFO →One CFO-grade Excel tip a week
A short, practical email for finance operators — functions, shortcuts, and the moves that save an afternoon. Free, unsubscribe anytime.