How to use the SIGN function
What is the SIGN function?
The SIGN function returns the sign of a number. 1 for a positive number, 0 (zero) for a 0 (zero) and -1 for a negative number.
What is the sign of a number?
The sign indicates if a number is positive or negative with one exception and that is zero. Positive numbers can be written with or without a plus sign and are larger than 0 (zero).
Negative numbers are always written with a minus sign are are smaller than 0 (zero).
Is zero positive or negative?
Zero is neither positive nor negative.
What is a signed number?
Positive and negative numbers are sometimes called signed numbers.
How to remove the negative sign from a number?
The ABS function returns the absolute value, in other words, the minus sign is removed.
How to toggle the sign?
Multiply by -1. For example,
3*-1 = -3
-2*-1 = 2
You can also use the minus sign like this: =-A1 This alternates the sign of a number specified in cell A1.
Alternative way to check if a number is larger than 0 (zero)?
Excel has comparison operators that you can use to check cell values against a condition.
- < less than sign
- > larger than sign
- = equal sign
Use the following formula to check if cell A1 contains a positive number:
=A1>0
It returns a Boolean value TRUE or FALSE if the condition is met.
SIGN Function Syntax
The SIGN function has only one argument.
SIGN(number)
SIGN Function Arguments
number | Required. A number for which you want to know the sign of. |
SIGN function example
The image above shows the SIGN function in cells C3:C8 and the source data in cells B3:B8. The first number specified in cell B3 is 23 which is a positive number, the SIGN function returns 1 in cell C3 indicating the number is positive.
The second number in cell B4 is -23 which the SIGN function evaluates to -1. This means the number is negative.
Formula in cell C3:
The third number is 0 (zero) which the SIGN function calculates to 0 (zero). The fourth and fifth number are Boolean values and are evaluated to TRUE - 1 and FALSE - 0 (zero).
What is a Boolean value?
A Boolean value in Excel is a value that can only be TRUE or FALSE. It represents binary logic and is the result of a logical expression using logical operators or a result of a few Excel functions that I'll discuss below.
Mastering Boolean logic and logical expressions is key to manipulating data and controlling workflow in Excel.
SIGN function not working
The SIGN function converts numbers stored as text automatically which is demonstrated in cell C3. The source data in cell B3 is '23 which Excel interprets as a text value, however, it also understands that it might be a mistake indicated by the green arrow located in the top left corner of cell B3.
The SIGN function cand handle error values and returns an error value, this is shown in cell B4 and B5.
Text values return a #VALUE! error demonstrated in cell B5 and B6.
Compare signs of values
Formula in cell D3:
=SIGN(B3)=SIGN(B4)
Filter values by sign
Dynamic array formula in cell D3:
=FILTER(B3:B16,SIGN(B3:B16)=1)
Count values by sign
Dynamic array formula in cell D3:
=LET(y,SIGN(B3:B16),x, UNIQUE(y), HSTACK(x,DROP(FREQUENCY(y,x),-1)))
Explaining formula in cell D3
HSTACK(UNIQUE(SIGN(B3:B16)),DROP(FREQUENCY(SIGN(B3:B16),UNIQUE(SIGN(B3:B16))),-1))
Step 1 - Calculate sign of number
The SIGN function returns the sign of a number. 1 for a positiv number, 0 (zero) for a 0 (zero) and -1 for a negative number.
Function syntax: SIGN(number)
SIGN(B3:B16)
becomes
SIGN({-22;-41;-31;38;0;-7;-43;38;-1;-1;31;45;20;44})
and returns
{-1; -1; -1; 1; 0; -1; -1; 1; -1; -1; 1; 1; 1; 1}
Step 2 - Create a unique distinct list of signs
The UNIQUE function returns a unique or unique distinct list.
Function syntax: UNIQUE(array,[by_col],[exactly_once])
UNIQUE(SIGN(B3:B16))
becomes
UNIQUE({-1; -1; -1; 1; 0; -1; -1; 1; -1; -1; 1; 1; 1; 1})
and returns
{-1;1;0}
Step 3 - Calculate the frequency of each sign
The FREQUENCY function calculates how often values occur within a range of values and then returns a vertical array of numbers.
Function syntax: FREQUENCY(data_array, bins_array)
FREQUENCY(SIGN(B3:B16),UNIQUE(SIGN(B3:B16)))
becomes
FREQUENCY({-1; -1; -1; 1; 0; -1; -1; 1; -1; -1; 1; 1; 1; 1},{-1;1;0})
and returns
{7;6;1;0}
Step 4 - Remove the last value
Delete the last value in the ouput array from the FREQUENCY function.
The DROP function removes a given number of rows or columns from a 2D cell range or array.
Function syntax: DROP(array, rows, [columns])
DROP(FREQUENCY(SIGN(B3:B16),UNIQUE(SIGN(B3:B16))),-1)
becomes
DROP({7;6;1;0},-1)
and returns
{7;6;1}
Step 5 - Stack arrays horizontally
The HSTACK function combines cell ranges or arrays. Joins data to the first blank cell to the right of a cell range or array (horizontal stacking)
Function syntax: HSTACK(array1,[array2],...)
HSTACK(UNIQUE(SIGN(B3:B16)),DROP(FREQUENCY(SIGN(B3:B16),UNIQUE(SIGN(B3:B16))),-1))
becomes
HSTACK({-1;1;0},{7;6;1})
and returns
{-1,7;1,6;0,1}
Step 6 - Shorten formula
The LET function lets you name intermediate calculation results which can shorten formulas considerably and improve performance.
Function syntax: LET(name1, name_value1, calculation_or_name2, [name_value2, calculation_or_name3...])
HSTACK(UNIQUE(SIGN(B3:B16)),DROP(FREQUENCY(SIGN(B3:B16),UNIQUE(SIGN(B3:B16))),-1))
y - SIGN(B3:B16)
x - UNIQUE(y)
LET(y,SIGN(B3:B16),x, UNIQUE(y), HSTACK(x,DROP(FREQUENCY(y,x),-1)))
'SIGN' function examples
The formula in cell B6 counts how many times the string (D3) is found in a cell value (B3) even […]
Table of Contents Count a specific text string in a cell Count text string in a range (case sensitive) Count […]
Functions in 'Math and trigonometry' category
The SIGN function function is one of many functions in the 'Math and trigonometry' category.
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form