Math & Trig

SIGN Function in Excel

Returns the sign of a number: 1 for positive, -1 for negative, 0 for zero.

Syntax

  • =SIGN(number)

Arguments

  • number (required): Any real number

Examples

  • =SIGN(10) - Sign of positive number - Result: 1
  • =SIGN(-5) - Sign of negative number - Result: -1
  • =SIGN(0) - Sign of zero - Result: 0

Excel SIGN function documentation

  • SIGN returns 1 if positive, 0 if zero, -1 if negative.
  • Syntax: =SIGN(number). Simple logic helper for direction and gain/loss flags.
  • Use in financial models, error direction checks, and compact IF replacements.
  • Related: [ABS](/functions/abs/), [IF](/functions/if/), [MAX](/functions/max/).

SIGN syntax and edge cases

  • number: any numeric value or cell reference.
  • Text numbers may coerce; true text → #VALUE!.
  • SIGN does not change magnitude — combine with ABS for absolute value with direction stored separately.
  • Floating point near-zero may not return exactly 0 — use ROUND if needed.

Step-by-step: profit/loss direction column

  • Step 1 — P&L values in column B (positive profit, negative loss).
  • Step 2 — =SIGN(B2) returns 1, 0, or -1.
  • Step 3 — Label: =IF(SIGN(B2)=1,"Gain",IF(SIGN(B2)=-1,"Loss","Flat")).
  • Step 4 — Sum gains: =SUMIF(sign_helper, 1, amounts).
  • Step 5 — Alternative: =IF(B2>0,"Gain",IF(B2<0,"Loss","Flat")) more readable for teams.

SIGN vs IF vs ABS

  • SIGN — compact direction only.
  • IF — readable labels for stakeholders.
  • ABS — magnitude without sign.
  • Math category: [/categories/math-trig/](/categories/math-trig/).

Worked examples to copy

  • Direction: =SIGN(A2).
  • Same sign check: =SIGN(A2)=SIGN(B2).
  • With ABS: =SIGN(x)*ABS(x) returns x for numeric x.
  • Zero test: =SIGN(A2)=0.

People also ask

  • SIGN of zero? — Returns 0.
  • SIGN on text? — #VALUE! unless coercible number.
  • SIGN vs SGN? — Excel uses SIGN; SGN in VBA.
  • SIGN in conditional format? — Less common than direct >0 tests.

Common errors

  • None common

Use cases

  • Direction indicators
  • Conditional logic
  • Data classification

Frequently asked questions

  • How do I use SIGN to show up/down arrows for changes? Use =IF(SIGN(A1)=1,"↑",IF(SIGN(A1)=-1,"↓","→")) to show arrows based on value. Or use SIGN directly with CHOOSE: =CHOOSE(SIGN(A1)+2,"↓","→","↑") where -1+2=1, 0+2=2, 1+2=3.
  • How do I preserve the sign when taking absolute value? Store the sign separately: sign = SIGN(A1), magnitude = ABS(A1). To reconstruct: =SIGN(A1)*ABS(A1) equals A1. Useful when you need to process magnitude but restore direction later.
  • Can SIGN help classify positive, negative, and zero values? Yes! SIGN returns exactly -1, 0, or 1, making it perfect for classification. Use with CHOOSE or SWITCH: =SWITCH(SIGN(A1),-1,"Loss",0,"Break-even",1,"Profit") categorizes financial results.

Editorial review

  • Reviewed by Excel.Directory Editorial Team. Updated May 2026.

When to use SIGN

  • Direction indicators — common Math & Trig scenario for SIGN.
  • Conditional logic — common Math & Trig scenario for SIGN.
  • Data classification — common Math & Trig scenario for SIGN.

SIGN in the Math & Trig category

  • Browse all Math & Trig functions at /categories/math-trig/ for related formulas.
  • SIGN syntax: =SIGN(number)
  • number (required): Any real number
  • Confirm SIGN arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.

Formula checklist before you copy down

  • Confirm SIGN arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.
  • If results look wrong, check for text stored as numbers and invisible spaces with TRIM.
  • Spot-check three known input rows manually against expected output.

Related Excel functions

  • ABS (/functions/abs/): Returns the absolute value of a number (the number without its sign).
  • IF (/functions/if/): Returns one value if a condition is TRUE and another value if it's FALSE.

Errors to watch for

  • None common — review causes on linked error pages in the directory.

Copy-paste audit workflow

  • Enter SIGN on three test rows with known expected output documented on a QA tab.
  • Fill down only after absolute references are locked on lookup tables and rate tables.
  • Compare against manual calculation or a calculator for financial and statistical functions.
  • Search this directory for comparison guides when choosing between similar functions in the same category.

SIGN worked examples to copy

  • =SIGN(10) — Sign of positive number. Expected result: 1.
  • =SIGN(-5) — Sign of negative number. Expected result: -1.
  • =SIGN(0) — Sign of zero. Expected result: 0.

SIGN reference summary for crawlers and offline review

  • SIGN belongs to the Math & Trig category in Excel. Returns the sign of a number: 1 for positive, -1 for negative, 0 for zero.
  • Full syntax: =SIGN(number). Open /functions/sign/ for parameters, FAQs, and related pages.
  • Common mistakes: None common
  • Pair this function with comparison guides when another Excel formula might fit the same task better.
  • Review fix-excel-formula-errors when unexpected errors appear after upgrading Excel or sharing across locales.