The SIGN function in Excel is a simple yet powerful tool that determines whether a number is positive, negative, or zero. It returns 1 for positive numbers, -1 for negative numbers, and 0 for zero. This tutorial will guide you through its usage with clear examples and practical applications.
How SIGN Works
SIGN Function Returns:
Returns: -1
Returns: 0
Returns: 1
Syntax
SIGN(number)
- number: A numeric value whose sign you want to determine (required).
Return Values
| If Number Is… | SIGN Returns | Example |
|---|---|---|
| Positive (> 0) | 1 | SIGN(25) = 1 |
| Negative (< 0) | -1 | SIGN(-25) = -1 |
| Zero (= 0) | 0 | SIGN(0) = 0 |
SIGN Function Examples
Example 1: Basic Positive Number
To determine the sign of 42:
=SIGN(42)
This will return 1 (positive number).
Example 2: Basic Negative Number
To determine the sign of -17:
=SIGN(-17)
This will return -1 (negative number).
Example 3: Zero Value
To determine the sign of zero:
=SIGN(0)
This will return 0 (zero has no sign).
Example 4: With Cell Reference
To check if the value in cell A1 is positive, negative, or zero:
=SIGN(A1)
Returns 1, -1, or 0 based on the value in A1.
Example 5: With Formula Result
To determine the sign of a profit/loss calculation:
=SIGN(B2-C2)
If B2 (revenue) minus C2 (costs) is positive, returns 1; if negative, returns -1; if break-even, returns 0.
Interactive SIGN Demo
Practical Applications
Common Use Cases:
- Financial analysis: Determine if transactions are income or expenses
- Trend analysis: Identify whether values are increasing or decreasing
- Data validation: Check if calculations result in expected positive/negative outcomes
- Conditional formatting: Apply different formats based on sign
- Inventory management: Identify surplus (positive) or shortage (negative)
- Performance tracking: Flag gains versus losses
SIGN with Other Functions
Example 1: Convert to Text Labels
Use SIGN with IF to create descriptive labels:
=IF(SIGN(A1)=1,”Profit”,IF(SIGN(A1)=-1,”Loss”,”Break Even”))
This converts numeric signs to readable text.
Example 2: Conditional Calculations
Apply different multipliers based on sign:
=A1*(1+SIGN(A1)*0.05)
This adds 5% to positive numbers and subtracts 5% from negative numbers.
Example 3: Count Positive vs Negative
Count how many values in a range are positive:
=SUMPRODUCT((SIGN(A1:A10)=1)*1)
This counts all positive values in the range.
Example 4: Absolute Difference Direction
Determine which of two values is larger:
=SIGN(A1-B1)
Returns 1 if A1 > B1, -1 if A1 < B1, and 0 if equal.
Real-World Business Examples
Business Case 1: Profit/Loss Indicator
Scenario: Create a quick indicator for profit (revenue – expenses):
=SIGN(C2-D2)
Result: 1 for profit, -1 for loss, 0 for break-even. Can be used for conditional formatting.
Business Case 2: Inventory Status
Scenario: Determine if inventory is above or below reorder point:
=SIGN(A2-B2)
Where A2 is current stock and B2 is reorder level. Returns 1 (adequate), -1 (reorder needed), or 0 (at threshold).
Business Case 3: Variance Analysis
Scenario: Flag whether actual sales exceeded budget:
=IF(SIGN(E2-F2)=1,”Over Budget”,IF(SIGN(E2-F2)=-1,”Under Budget”,”On Target”))
Result: Clear text indicator of budget performance.
Business Case 4: Temperature Change Direction
Scenario: Determine if temperature increased, decreased, or stayed the same:
=SIGN(G2-G1)
Result: 1 for increase, -1 for decrease, 0 for no change.
SIGN vs Related Functions
| Function | Purpose | Example Result |
|---|---|---|
| SIGN(-5) | Returns sign indicator | -1 |
| ABS(-5) | Returns absolute value | 5 |
| INT(-5.7) | Rounds down to integer | -6 |
| TRUNC(-5.7) | Removes decimal part | -5 |
Tips and Tricks
- Simple logic: SIGN is faster than using multiple IF statements to check positive/negative.
- Multiplication trick: Multiply SIGN result by a value to apply it directionally (e.g., fees).
- Combining with ABS: Use SIGN(A1)*ABS(B1) to transfer sign from one number to another.
- Comparison shortcut: SIGN(A-B) is a quick way to compare two values.
- Non-numeric values: SIGN returns #VALUE! error for text or blank cells.
- Zero is neutral: Remember that zero returns 0, not 1 or -1.
- Conditional formatting: Use =SIGN(A1)=1 as a rule to highlight positive values.
- Array formulas: SIGN works with arrays for batch sign determination.
Advanced Techniques
Technique 1: Directional Scaling
Apply different percentage changes based on whether value is positive or negative:
=A1*(1+SIGN(A1)*0.1)
Increases positive values by 10% and decreases negative values by 10%.
Technique 2: Sign Transfer
Copy the sign from one number to another:
=ABS(A1)*SIGN(B1)
Takes the absolute value of A1 and gives it the same sign as B1.
Technique 3: Categorize Changes
Categorize values into gain, loss, or neutral buckets:
=CHOOSE(SIGN(A1)+2,”Loss”,”Neutral”,”Gain”)
SIGN returns -1, 0, or 1; adding 2 gives 1, 2, or 3 for CHOOSE function.
Technique 4: Sum Only Positive or Negative Values
Sum only positive values in a range:
=SUMPRODUCT((SIGN(A1:A10)=1)*A1:A10)
This sums only the positive values, ignoring negative and zero values.