SIGN function
Quick answer
SIGN tells you which side of zero a number is on: 1 for positive, -1 for negative and 0 for zero.
- Syntax
=SIGN(number)- Most common use
=SIGN(C2-B2)returns 1 (Up on last year: 1)
It turns a change into a direction (up, down or flat) that you can count, chart or turn into a label.
- Available in
- Every current version
- Category
- Math functions
- Returns
- 1, -1 or 0.
- Google Sheets
- Works the same in Google Sheets.
Syntax
=SIGN(number)
| Argument | Required? | What it is |
|---|---|---|
number | Required | The number or calculation to test. |
Examples
Each result below is calculated from the sample sheet by our formula engine, the same one that grades the practice.
| Cell | Formula | Result | What it shows |
|---|---|---|---|
| D2 | =SIGN(C2-B2) | 1 | Up on last year: 1 |
| D3 | =SIGN(C3-B3) | -1 | Down on last year: -1 |
| D4 | =SIGN(C4-B4) | 0 | No change: 0 |
Try it live
This is the sample sheet. Change any number or text, or edit a formula, and the results update.
Show the sample data as a table
| Month | Last year | This year | Direction |
| Jan | 420 | 455 | 1 |
| Feb | 390 | 372 | -1 |
| Mar | 410 | 410 | 0 |
Practice SIGN
Graded instantly, then re-checked on changed data.
Which way did prices move?
D2, use SIGN to show whether each product's price went up (1), down (-1) or stayed the same (0). Fill down to D6.Common mistakes
| What you see | Why, and how to fix it |
|---|---|
| Got 0 when you expected a direction | The two values are equal, or both cells are empty (empty cells count as 0). Check the inputs. |
| You want words, not numbers | Wrap SIGN in CHOOSE: =CHOOSE(SIGN(C2-B2)+2,"Down","Flat","Up"). |
| #VALUE! | One of the cells holds text. Make sure both values are numbers. |
Tips
- Count how many months grew: =COUNTIF(D2:D13,1).
Questions
What does the SIGN function return for zero?
0. Positive numbers return 1 and negative numbers return -1.
How do I show up or down labels from a change?
Use =CHOOSE(SIGN(C2-B2)+2,"Down","Flat","Up"), or an IF formula, or conditional formatting icon sets.
Answers that use SIGN
In other languages
SIGN is SIGNO in Spanish Excel and VORZEICHEN in German Excel. Each page has the syntax with semicolons, the examples and the exercise in that language. For other functions and languages, use the Function Translator.
Updated Checked by running: every formula on this page was run by our formula engine on .