SIGN Function (LibreOffice Calc)
The SIGN function in LibreOffice Calc returns the sign of a number: 1 for positive numbers, -1 for negative numbers, and 0 for zero. Learn syntax, examples, common errors, and best practices.
Compatibility
▾| Excel | ✔ |
| Gnumeric | ✔ |
| Google_sheets | ✔ |
| Libreoffice | ✔ |
| Numbers | ✔ |
| Onlyoffice | ✔ |
| Openoffice | ✔ |
| Wps | ✔ |
| Zoho | ✔ |
What the SIGN Function Does ▾
- Returns
1for positive numbers - Returns
-1for negative numbers - Returns
0when the number is zero - Works with cell references, formulas, and expressions
- Useful for directional logic, normalization, and modeling
- Often paired with ABS for magnitude + direction calculations
It is designed to be fast, predictable, and universally compatible.
Syntax ▾
SIGN(number)
Arguments
- number:
Any numeric value, cell reference, or expression.
Examples:-10A1A1 - B1SUM(A1:A5)
Basic Examples ▾
Determine the sign of a positive number
=SIGN(25)
Returns 1.
Determine the sign of a negative number
=SIGN(-12)
Returns -1.
Determine the sign of zero
=SIGN(0)
Returns 0.
Use SIGN with a cell reference
=SIGN(A1)
Returns the sign of the value in A1.
Advanced Examples ▾
Normalize a value to ±1
=SIGN(A1)
Useful for direction-only logic.
Apply the sign of one value to the magnitude of another
=ABS(A1) * SIGN(B1)
Returns the magnitude of A1 with the sign of B1.
SIGN with IF for directional messages
=IF(SIGN(A1) = 1; "Positive"; IF(SIGN(A1) = -1; "Negative"; "Zero"))
Classifies the value in A1.
SIGN with calculations
=SIGN(A1 - B1)
Returns the direction of the difference between A1 and B1.
SIGN with SUM
=SIGN(SUM(A1:A10))
Determines whether the total is positive, negative, or zero.
SIGN for financial modeling
=SIGN(A1) * 100
Applies direction to a fixed magnitude.
Common Errors and Fixes ▾
SIGN returns 0 unexpectedly
Possible causes:
- The input expression evaluates to 0
- A number is stored as text
- A referenced cell is empty
Fix:
Convert text to numbers using:
Data → Text to Columns → OK
SIGN returns 1 or -1 incorrectly
Often caused by:
- Hidden spaces in numeric text
- Incorrect arithmetic in the input expression
- Cell formatting set to text
Err:502 — Invalid argument
Occurs when:
- The argument is non‑numeric text
- A formula returns an error passed into SIGN
Fix:
Wrap the expression with IFERROR:
IFERROR(SIGN(A1); 0)
Best Practices ▾
- Use SIGN when direction matters more than magnitude
- Pair SIGN with ABS for normalized directional calculations
- Use SIGN to simplify IF logic for positive/negative checks
- Avoid mixing SIGN with text-formatted numbers
- Use SIGN to detect the direction of change in comparisons
Related Patterns and Alternatives ▾
- Use ABS to get magnitude without direction
- Use ROUND, INT, or TRUNC to control numeric precision
- Use A1 > 0, A1 < 0, or A1 = 0 for direct comparisons
- Use POWER(number; 2) and SQRT for distance calculations
By mastering SIGN and its combinations with other math functions, you can build precise, direction-aware numeric models in LibreOffice Calc that behave consistently across all data scenarios.