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

Functions in 'Math and trigonometry' category

The SIGN function function is one of many functions in the 'Math and trigonometry' category.

Excel function categories

Excel categories

Leave a Comment