Why does SIGN only return 1, 0, or −1? Can I get different values?

SIGN is mathematically defined to return exactly those three values. To map them to custom outputs, wrap SIGN in CHOOSE—e.g., CHOOSE(SIGN(x)+2, 'Low', 'Equal', 'High')—or use nested IFs for more complex logic. Most use cases work with the native 1/0/−1 values directly.

How is SIGN different from ABS?

ABS returns absolute value (magnitude only, always ≥ 0), erasing direction. SIGN extracts and returns direction only (positive/negative/zero), erasing magnitude. Use SIGN when you care about above-or-below a threshold, and ABS when you need size without regard to sign.

Can SIGN work with dates or time differences?

Yes—dates are stored as numbers in spreadsheets. SIGN(EndDate − StartDate) returns 1 if the interval is positive, −1 if negative (end before start), and 0 if identical. This is useful for validating whether deadlines were met or whether records are in chronological order.

When should I use SIGN instead of IF?

SIGN is more concise for testing a single value's sign. IF is better when you need text outcomes, multiple conditions, or complex logic branches. For simple 'is this above, equal to, or below target?' questions, SIGN is cleaner and faster to evaluate.

SIGN function

SIGN returns the mathematical sign of a number—positive returns 1, negative returns -1, and zero returns 0, enabling quick classification of values.

=SIGN(number)

Generate a SIGN formula

Describe what you need. The generator will reach for SIGN where SIGN is the right tool, and tell you when it is not.

Arguments

How SIGN reads its arguments

Argument	Required	Description
number	Required	A numeric value to test. If number is positive, SIGN returns 1; if negative, returns -1; if zero, returns 0. Text arguments cause a #VALUE! error.

Returns

Integer: 1 for positive numbers, -1 for negative numbers, 0 for zero.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Classify tickets by satisfaction score

Ticket ID	Agent	CSAT
TK001	Alice	5
TK005	Bob	1

=SIGN(E2-3)

Result: 1, -1

The formula subtracts the satisfaction target (3) from the actual CSAT score. TK001 with CSAT 5 returns 1 (exceeds target). TK005 with CSAT 1 returns -1 (falls short). A result of 0 would indicate the CSAT exactly met the target threshold. This enables fast classification of customer satisfaction without nested IF statements.

2. Classify resolution time against SLA

Ticket ID	Opened	Closed
TK001	2026-09-01	2026-09-02
TK002	2026-09-02	2026-09-04
TK003	2026-09-03	2026-09-06

=SIGN(2-(C2-B2))

Result: 1, 0, -1

The formula subtracts actual resolution time (Closed − Opened) from the 2-day SLA target. TK001 resolved in 1 day, so SIGN(2−1) = 1 (beat SLA). TK002 took exactly 2 days, so SIGN(2−2) = 0 (met SLA). TK003 took 3 days, so SIGN(2−3) = −1 (missed SLA). This instantly classifies ticket performance for reporting.

3. Identify above-average priority tickets

Ticket ID	Priority	Agent
TK001	3	Alice
TK002	2	Bob
TK004	1	Charlie

=SIGN(B2-2.6)

Result: 1, -1, -1

The formula subtracts the team's average priority (2.6) from each ticket's priority. TK001 at priority 3 returns 1 (above average). TK002 at priority 2 and TK004 at priority 1 both return -1 (below average). This helps routing specialists spot high-impact tickets that exceed normal workload demands.

Common errors

Which SIGN error are you seeing?

Error	Why it happens	How to fix it
#VALUE!	Passing text instead of a number—e.g., =SIGN(Agent) where the Agent column contains names like 'Alice'.	Replace the text reference with a numeric column (Priority, CSAT), or convert text to a number with COLUMN(), ROW(), or a lookup function first.
#REF!	The formula references a deleted or moved column—e.g., =SIGN(E2-3) if column E was deleted after the formula was entered.	Verify that all cell references in the formula still exist. Use the Name Manager to check for broken links, or re-enter the formula with current cell addresses.
#N/A	The argument contains an error value from an upstream formula—e.g., =SIGN(VLOOKUP(...)) where the VLOOKUP returns #N/A because no match exists.	Wrap the SIGN formula in IFERROR(SIGN(...), default_value) to handle missing values, or fix the upstream formula that is producing the error.

Tips and when to use something else

Use SIGN to classify metrics as above/below/equal to a threshold without nested IF statements—perfect for SLA checks and performance tiers.
SIGN returns only three values (1, 0, −1), making it ideal for coding positive/neutral/negative outcomes in compact formulas.
Don't use SIGN on ranges directly; instead use SUMPRODUCT(SIGN(...)) or array formulas to sum multiple classification results.
For simple sign classification, SIGN is more concise than IF. But if you need text labels like 'Good' or 'Poor', use IF with SIGN as the condition instead.

Related functions

ABSABS returns the absolute value of any number, removing the sign to show only magnitude, useful for comparing distances regardless of direction.IFThe IF function returns one value if a condition is true and another (or nothing) if it is false, letting you branch logic in a cell.SUMPRODUCTSUMPRODUCT multiplies arrays element-by-element and sums the results, useful for weighted totals and multi-criteria counting.ROUNDROUND returns a number rounded to a specified number of decimal places, using standard rounding rules (0.5 rounds up).QUOTIENTReturns the integer quotient of a division operation, discarding the remainder—useful for finding complete groups or batches from a total.

Frequently asked questions

Why does SIGN only return 1, 0, or −1? Can I get different values?: SIGN is mathematically defined to return exactly those three values. To map them to custom outputs, wrap SIGN in CHOOSE—e.g., CHOOSE(SIGN(x)+2, 'Low', 'Equal', 'High')—or use nested IFs for more complex logic. Most use cases work with the native 1/0/−1 values directly.
How is SIGN different from ABS?: ABS returns absolute value (magnitude only, always ≥ 0), erasing direction. SIGN extracts and returns direction only (positive/negative/zero), erasing magnitude. Use SIGN when you care about above-or-below a threshold, and ABS when you need size without regard to sign.
Can SIGN work with dates or time differences?: Yes—dates are stored as numbers in spreadsheets. SIGN(EndDate − StartDate) returns 1 if the interval is positive, −1 if negative (end before start), and 0 if identical. This is useful for validating whether deadlines were met or whether records are in chronological order.
When should I use SIGN instead of IF?: SIGN is more concise for testing a single value's sign. IF is better when you need text outcomes, multiple conditions, or complex logic branches. For simple 'is this above, equal to, or below target?' questions, SIGN is cleaner and faster to evaluate.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17