SIGN

What it does

SIGN(number)

SIGN(number) is the signum function:

sgn(x) =  1  for x > 0
          0  for x = 0
         -1  for x < 0

Microsoft's page states exactly that: it "Returns 1 if the number is positive, zero (0) if the number is 0, and -1 if the number is negative", with =SIGN(10) giving 1, =SIGN(4-4) giving 0, and =SIGN(-0.00001) giving -1.

• Domain: all real numbers; the documentation says "any real number".
• Range: the three-element set {-1, 0, 1}.
• Parity: odd, sgn(-x) = -sgn(x).
• Structure: sgn is the derivative of |x| away from the origin, and x = sgn(x) * |x| is the polar decomposition of a real number. sgn is discontinuous at 0 — the only discontinuity, a jump of size 2 — and that single point is where every implementation question lives.
• Identities worth having: SIGN(x) * ABS(x) = x; SIGN(x)^2 is 1 except at zero; ABS(SIGN(a) - SIGN(b)) is a sign-change detector, which is the classic bisection guard.

The three-valued convention is worth naming explicitly because it is not universal. Some languages define sign as two-valued (±1, with zero going to +1), and hardware sign-bit extraction is two-valued by construction. Excel's documented function is the three-valued mathematical signum.

Arguments

There is no tolerance argument and no epsilon. SIGN compares against exact zero, which is the source of essentially every surprise it produces (see Numerical notes).

Try it

Quirks

Result and edge cases

Returns Number, one of -1, 0, 1.

• Zero. The documented result is 0. The interesting question is negative zero: IEEE 754 has two zeros, +0 and -0, which compare equal to each other and to nothing else. Under a comparison-based signum, SIGN(-0) is 0; under a sign-bit-based one it would be -1. Microsoft's page does not address the case. The Handbook does not assert an answer, and this is the first probe listed below. Producing a -0 argument in a worksheet is easy enough — -1 * 0, or 0 * -1, or a formula that underflows from below.
• Subnormals. A subnormal is a nonzero number, so SIGN of one is ±1. The documented example SIGN(-0.00001) makes the general point: magnitude is irrelevant, only the sign matters.

Errors

Microsoft's page lists no error conditions for SIGN, and no domain restriction exists — the function is total on the reals.

How to implement

Numerical notes

SIGN performs no arithmetic, so it has no rounding error of its own. Everything that goes wrong with it goes wrong upstream, and there are three recurring patterns worth naming.

1. The zero case is almost never the zero you mean. SIGN(0.1 + 0.2 - 0.3) is not 0, because 0.1 + 0.2 - 0.3 is not 0 in binary64. Any workflow that uses SIGN to test "did this quantity come out to zero" is testing an exact bit condition and will get ±1 on values that are zero to any reasonable tolerance. The remedy is not to fix SIGN; it is to test ABS(x) < tol explicitly, so that the tolerance is visible in the formula rather than implied.

2. SIGN is a discontinuity amplifier. Its condition number is infinite at the origin: an arbitrarily small perturbation of the input changes the output by 1 or 2. That is inherent to the mathematics, not a defect, but it means SIGN should never sit downstream of a long accumulation whose last bits are noise. The classic failure is a sign-change test on the residual of an iterative calculation, where the residual's own error exceeds its value near convergence — the bisection loop then wanders because SIGN is reporting the sign of the rounding error.

3. Sign-bit extraction and signum are different functions. They agree everywhere except at zero, where signum gives 0 for both zeros and sign-bit extraction gives +1/-1 respectively. An implementation written with copysign or a bit mask gets the negative-zero case wrong relative to the documented three-valued definition; an implementation written with two comparisons gets it right. Since the difference shows up on exactly one input class, it survives casual testing indefinitely. This is the reason the negative-zero probe below is worth running even though the case looks exotic.

What a careful implementation does: two comparisons against literal zero, in the order x > 0, then x < 0, then 0. Under IEEE comparison semantics this handles both zeros correctly and needs no special case. It is three lines and there is no cleverer version worth having.

Rust code

call(SIGN, "SIGN", 1, NUMBER, sign)

/// SIGN: 1 for a positive number, -1 for a negative one, 0 for zero.
fn sign(
    arguments: &[CalcValue],
    services: &mut EvalServices<'_>,
) -> Result<CalcValue, FunctionFault> {
    numeric(SIGN, arguments, services, |_, n| {
        Ok(if n[0] > 0.0 {
            1.0
        } else if n[0] < 0.0 {
            -1.0
        } else {
            0.0
        })
    })
}

No implementation has been published with a Handbook test suite for this function.

Version differences

The recorded environments below distinguish a reading from a same-input comparison. Counts describe captured inputs in each environment; overlapping captures and comparisons are not totals of unique worksheet calls.

• XC-SIGN-001: build 19929 versus build 20430; 2 same-input pairs, 0 differing answers. Compared axes: excel-build. Numeric answers were compared by their recorded binary64 bits; other answers by typed value.

Agreement on these compared inputs does not establish agreement on unmeasured inputs.

Recorded environments and comparison details.

Alternatives

Relationships

• ABS — the other half of the decomposition x = SIGN(x) * ABS(x). The two are almost always used together.
• IF(x>0, 1, IF(x<0, -1, 0)) — the hand-written equivalent, and worth mentioning because it is the form readers write when they have forgotten SIGN exists. It also makes the comparison semantics explicit, which SIGN hides.
• GESTEP and DELTA (engineering category) — the other threshold functions. DELTA(a, b) is an equality indicator and GESTEP(x, step) a step function; between them and SIGN there are three different conventions for what happens exactly at the boundary, and they do not agree with each other.
• TANH — the smooth saturating analogue. Where SIGN is used to steer a computation, TANH(k*x) for large k is the differentiable stand-in, which is why it turns up in optimization work.
• TRUNC, INT, ROUND — the rounding family shares SIGN's sensitivity to which side of zero a value falls on; TRUNC in particular is "round toward zero", i.e. rounding in the direction opposite to SIGN.
• Confused with: ABS (magnitude, not direction) and with a two-valued sign, which is what most programming languages' bit-level helpers return.

Named variants

No named variants are recorded yet.