Math
SIGN function: classify variances into +1, 0, or -1 directional flags
Turn any numeric delta into -1, 0, or +1 with SIGN to map status labels via CHOOSE or count net gains vs declines.
Replace nested IFs for three-way variance labels
SIGN(number) returns 1 when number > 0, 0 when number = 0, and -1 when number < 0. Adding 2 shifts (-1, 0, 1) to (1, 2, 3), letting CHOOSE(SIGN(Actual - Target) + 2, "Below", "On Target", "Above") assign a three-state label in one compact expression.
| Region (A) | Target (B) | Actual (C) | SIGN(C - B) | Label |
|---|---|---|---|---|
| East | 200 | 225 | 1 | Above |
| West | 180 | 180 | 0 | On Target |
| South | 210 | 195 | -1 | Below |
Map variance direction with CHOOSE(SIGN(...) + 2, ...)
For East (225 - 200 = +25), SIGN returns 1, SIGN + 2 = 3, and CHOOSE picks "Above".
=CHOOSE(SIGN(C2 - B2) + 2, "Below", "On Target", "Above")Verify zero and negative transitions
Row 3 (180 vs 180) yields SIGN = 0 ("On Target"), and Row 4 (195 vs 210) yields SIGN = -1 ("Below").
=SIGN(C4 - B4)Try the example
Use the original values or change them to test your understanding.