Contact
12 programs
Explore the site

SIGN Function (OpenOffice Calc)

Math Beginner OpenOffice Calc OpenOffice.org 3.0

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 and examples below. Native application testing has not been documented for this guide. How formula examples are checked

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
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.