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.
Compatibility
▾| Excel | ✔ |
| Gnumeric | ✔ |
| Google_sheets | ✔ |
| Libreoffice | ✔ |
| Numbers | ✔ |
| Onlyoffice | ✔ |
| Openoffice | ✔ |
| Wps | ✔ |
| Zoho | ✔ |
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
SIGN does not return the magnitude — only the direction.
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)
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 ▾
SIGN returns Err:502 (Invalid argument)
Occurs when:
- Input is text
- Input is empty
- A malformed reference is used
SIGN returns unexpected results
Possible causes:
- Hidden spaces causing text instead of numbers
- Formula result is exactly zero
- Text numbers not converted to numeric
SIGN ignores values you expected it to include
SIGN ignores:
- Text numbers (
"123") - Empty cells
- Logical values
- Errors
SIGN includes values you expected it to ignore
SIGN includes:
- Dates
- Times
- Numeric results of formulas
Err:508 — Missing parenthesis
Usually caused by:
- Missing
) - Using commas instead of semicolons
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 is a powerful replacement for many IF statements —
SIGN(A1 - B1) instantly tells you whether A1 is greater, equal, or less than B1.