In a Nutshell
SIGN is a Microsoft Excel function that tells us the sign of a number (i.e. whether the number is positive, negative, or zero). It does this by returning either a 1 (for a positive number), 0 (for zero) or -1 (for a negative number).
It is categorised in the Math & Trig Function Library on the Formulas tab:
Syntax
SIGN(number)
The SIGN function syntax requires only one argument:
- number – The number that we want to determine the sign of.
Notes
- SIGN returns a -1 for all values less than zero.
- SIGN returns a 0 for zero.
- SIGN returns a 1 for all values greater than zero.
- SIGN returns an error for any non-numerical values
Examples
Let’s take a look at a few examples of the SIGN function in action. The example file can be downloaded here .
Example 1 – Comparing values
In the example below, we have sales figures for two financial years. In column E we determine whether sales have gone up or down by comparing the values in columns C and D. If sales went up, the ruslt is positive, so SIGN returns a 1, if sales went down, it’s negative, so it returns -1. If it stayed the same it returns a zero.
A few more quick examples
The table below (taken from the example workbook) highlights the results from a range of inputs, including numbers, text, formulas and even constants such as pi(, in row 21:
In case you missed it, here is a link to the example workbook.
Errors
- SIGN produces an error if the argument (the value in brackets) is not numerical. E.g. =SIGN(“five”) will produce an error, as “five” is a text string and =SIGN(5) should be entered instead.
Excel Versions Supported
Excel 2003 onwards