SIGN Function (OpenOffice Calc)
The SIGN function in OpenOffice Calc returns the sign of a number: -1, 0, or 1. Learn syntax, behavior, examples, and best practices.
Verification status: Consult the documentation, examples and any stated test scope below. How formula examples are checked
Availability and version history
- Introduced: Documented by Apache OpenOffice 4.1.16; original introduction not established.
- Availability: Supported in Apache OpenOffice 4.1.16, verified against its function registry. This identifies a supported release, not a final working version.
- Scope: Desktop Calc, using the English function name. Availability in other spreadsheet applications is separate.
- Checked: 2026-10-14 — function reference; versioned support. Documentation/source check; not a new native test. Existing example-specific test claims below retain their stated scope.
The SIGN function in OpenOffice Calc returns the sign of a number:
- 1 if the number is positive
- 0 if the number is zero
- -1 if the number is negative
SIGN is essential for direction‑based logic, normalization, math pipelines, and formulas that depend on whether a value is above, below, or equal to zero.
What the SIGN Function Does
- Returns -1, 0, or 1
- Indicates whether a number is negative, zero, or positive
- Works with integers, decimals, and formula results
- Useful for direction logic, comparisons, and normalization
- Works across sheets
SIGN is ideal when you need direction without magnitude.
Syntax
SIGN(number)
Arguments:
- number — Any numeric value or formula result
Behavior Summary
| Input | SIGN Output | Meaning |
|---|---|---|
| Positive number | 1 | Above zero |
| Zero | 0 | Exactly zero |
| Negative number | -1 | Below zero |
Basic Examples
Sign of a positive number
=SIGN(25)
Result: 1
Sign of zero
=SIGN(0)
Result: 0
Sign of a negative number
=SIGN(-12.5)
Result: -1
Sign of a formula result
=SIGN(A1 - B1)
Advanced Examples
Direction of change
=SIGN(Current - Previous)
Convert any number to ±1 (binary direction)
=IF(SIGN(A1)=1; 1; -1)
This binary convention maps zero to -1 as well as negative values. Keep SIGN(A1) itself when zero must remain a third outcome.
Normalize a value to its direction
=SIGN(A1)
Multiply by sign to flip direction
=A1 * SIGN(B1)
SIGN across sheets
=SIGN(Sheet1.A1)
SIGN for conditional formatting logic
Highlight negative values:
=SIGN(A1) = -1
SIGN for trend detection
=SIGN(A2 - A1)
SIGN for vector normalization (1D)
=SIGN(A1)
SIGN in array formulas
=SIGN(A1:A10)
Confirm with Ctrl+Shift+Enter.
SIGN + ABS = Reconstruct Original Value
You can rebuild any number using:
[ x = SIGN(x) \cdot ABS(x) ]
Calc:
=SIGN(A1) * ABS(A1)
Common Errors and Fixes
The callback initializes zero and changes it only when the numeric input is strictly below or above zero. It uses no approximate-equality tolerance. Errors from the numeric reader propagate through PushInt; they are not skipped. A physically empty referenced cell is read as zero by the inspected scalar reader, giving SIGN=0. This is distinct from certifying every formula-empty or imported missing value.
For scalar numeric functions the checked reader distinguishes genuine empty references, strings, numeric values and errors. It can parse some numeric text, including ordinary integer text, and reads a physically empty referenced cell as zero. Therefore neither “all text is ignored” nor “all blanks always produce an invalid-argument error” is a safe general rule. A formula error propagates; it is not silently removed. Use explicit numeric cells in the reproducible tests, and inspect data types before changing a domain check.
Formula parsing errors are separate from mathematical domain errors. Check the function spelling, parentheses and the semicolon separator used in these examples. Number formatting affects display; the separate Calc precision-as-shown setting can also affect numeric reads, so the reference calculations assume ordinary stored-value precision. Dates and times are stored numeric values, but that does not make applying this mathematical operation physically meaningful to every date serial.
Best Practices
- Use SIGN for direction‑based logic
- Use ABS when magnitude also matters
- Use SIGN(A1 - B1) for comparison without IF
- Convert imported text numbers to real numbers
- Use named ranges for cleaner formulas
SIGN(A1 - B1) instantly tells you whether A1 is greater, equal, or less than B1.
Reproducible numeric controls
| Formula | Independent reference result |
|---|---|
=SIGN(25) |
1 |
=SIGN(0) |
0 |
=SIGN(-12.5) |
-1 |
=SIGN(0.000001) |
1 |
=SIGN(-0.000001) |
-1 |
=SIGN(7-7) |
0 |
These are independent mathematical/source controls, not new native application or GUI observations. ERROR denotes an invalid or non-finite source path without inventing an observed localized message. Decimal expectations use the shown rounding precision; full stored results should be retained for subsequent arithmetic.
Complete worksheet and changed-input checks
Enter previous measurements 10,10,10,0 in A2:A5 and current measurements 12,10,8,-3 in B2:B5. In C2 enter =B2-A2 and fill down: differences are 2,0,-2,-3. In D2 enter =SIGN(C2) and fill down: directions are 1,0,-1,-1. In E2 enter =ABS(C2)*D2 and fill down; the original differences are reconstructed.
The direction removes magnitude. Changes -2 and -3 have the same sign but are not equal changes. Summing directions gives -1 here, while summing numerical changes gives -3. Label those summaries separately; they answer different questions.
Advanced validation and model boundaries
Define a dead band when measurement noise matters. With an agreed tolerance of 0.01, =IF(ABS(A2)<=0.01;0;SIGN(A2)) classifies -0.02,-0.01,0,0.01,0.02 as -1,0,0,0,1. This is a deliberate tolerance rule around SIGN, not a built-in rounding behavior. SIGN alone returns 1 for a tiny positive number even if the cell format displays 0.00.
Do not replace sign by unsafe division. For nonzero x, x/ABS(x) has the same mathematical sign. At zero that quotient is undefined, whereas SIGN(0)=0. A normalization pipeline should preserve or explicitly handle the zero case instead of introducing division by zero.
Documentation and verification scope
The original useful examples, named-variable illustrations, shortcodes and design are retained; variables such as Radius, Rate, sd or Time must be defined as cells/names with the required units before those templates are executable. The earlier cross-sheet and Ctrl+Shift+Enter examples describe formula patterns, not a new GUI or array-entry test. Compatibility metadata does not certify identical coercion or boundary behavior in other products.
See the official SIGN reference, 4.1.16 mathematics help, scalar elementary functions, SIGN and logarithm callbacks, POWER implementation, numeric argument reading, and non-finite result handling. The first help/core snapshots retain October 13 retrieval dates; POWER retains October 14. Numeric-reader and result-handler sources were retrieved October 15. Original introduction and final functioning version remain unknown; the supported 4.1.16 evidence above is separate.