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.

1. Introduction

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:

It returns a Boolean value TRUE or FALSE if the condition is met.

2. SIGN Function Syntax

The SIGN function has only one argument.

SIGN(number)

number

Required. A number for which you want to know the sign of.

3. 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.

4. 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.

5. Compare signs of numbers

This example shows a formula that compares the signs of two numbers, it returns TRUE if both numbers have the same sign and FALSE if the signs are different.

Formula in cell D3:

Explaining the formula

Step 1 - Extract the sign from the first number

SIGN(B3)

becomes

SIGN(23)

and returns 1.

Step 2 - Extract the sign from the second number

SIGN(B4)

becomes

SIGN(-23)

and returns -1.

Step 3 - Compare signs

The equal sign is a comparison operator and returns a boolean value TRUE if the condition is met and FALSE if the condition is not met. It lets you compare the signs of the numbers in cells B3 and B4.

SIGN(B3)=SIGN(B4)

becomes

1=-1

and returns FALSE.

6. Filter values by sign

This example demonstrates how to filter numbers based on their signs, cell range B3:B16 contains random integers both positive and negative signs. The FILTER function extracts the positive numbers based on their sign.

Dynamic array formula in cell D3:

Explaining the formula

Step 1 - Extract the signs from the numbers

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 - Compare output to 1

The equal sign is a comparison operator and returns a boolean value TRUE if the condition is met and FALSE if the condition is not met. We need to compare with 1 to extract positive numbers from cell range B3:B16.

SIGN(B3:B16)=1

becomes

{-1;-1;-1;1;0;-1;-1;1;-1;-1;1;1;1;1}=1

and returns

{FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE}

Step 3 - Filter values based on condition

The FILTER function extracts values/rows based on a condition or criteria.

Function syntax: FILTER(array, include, [if_empty])

FILTER(B3:B16,SIGN(B3:B16)=1)

becomes

FILTER({-22;-41;-31;38;0;-7;-43;38;-1;-1;31;45;20;44}, {FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE})

and returns

{38;38;31;45;20;44}

7. Extract records containing negative numbers

Array formula in B23:

copied down as far as needed and then copied to the right as far as needed.

Explaining array formula in cell B23

Step 1 - Filter negative values and return their corresponding row number

IF($D$3:$D$18<0, ROW($D$3:$D$18)-MIN(ROW($D$3:$D$18))+1, "")

becomes

IF({1;5;0;-2;-5;2;4;4;-3;-4;-4;1;-1;0;-5;1}<0, ROW($D$3:$D$18)-MIN(ROW($D$3:$D$18))+1, "")

becomes

IF({FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; TRUE; FALSE; TRUE; FALSE; TRUE; FALSE}, ROW($D$3:$D$18)-MIN(ROW($D$3:$D$18))+1, "")

becomes

IF({FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; TRUE; FALSE; TRUE; FALSE; TRUE; FALSE}, {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16}, "")

and returns

{"";"";"";4;5;"";"";"";9;10;11;"";13;"";15;""}

Step 2 - Return the k-th smallest row number

SMALL(IF($D$3:$D$18<0, ROW($D$3:$D$18)-MIN(ROW($D$3:$D$18))+1, ""), ROW(A1))

becomes

SMALL({"";"";"";4;5;"";"";"";9;10;11;"";13;"";15;""}, ROW(A1))

becomes

SMALL({"";"";"";4;5;"";"";"";9;10;11;"";13;"";15;""}, 1)

and returns 4.

Step 3 - Return value from cell range

INDEX($B$3:$D$18, SMALL(IF($D$3:$D$18<0, ROW($D$3:$D$18)-MIN(ROW($D$3:$D$18))+1, ""), ROW(A1)), COLUMN(A1))

becomes

INDEX($B$3:$D$18, 4, 1)

becomes

INDEX({"MARGARET","ROBINSON",1; "DAVID","DAVIS",5; "MICHAEL","JONES",0; "MARIA","GARCIA",-2; "LISA","RODRIGUEZ",-5; "PATRICIA","JACKSON",2; "MARY","THOMAS",4; "THOMAS","TAYLOR",4; "MICHAEL","JONES",-3; "RICHARD","MILLER",-4; "JOSEPH","MOORE",-4; "LINDA","WHITE",1; "BARBARA","HARRIS",-1; "MARY","THOMAS",0; "JOHN","JOHNSON",-5; "DOROTHY","CLARK",1} 4, 1)

and returns "Maria" in cell B23.

Get excel sample file for this tutorial.

extract negative values and adjacent cells.xls
(Excel 97-2003 Workbook *.xls)

8. Extract negative values and adjacent cells (Excel Filter)

1. Select B2:D18
2. Press with left mouse button on "Data" tab
3. Press with left mouse button on "Filter" on the Ribbon
4. Press with left mouse button on Black triangle in cell D2
   
5. Select "Number Filters"
6. Press with left mouse button on "Less Than..."
   
   
7. Type 0 (zero)
8. Press with left mouse button on OK!

9. Count values by sign

The following formula works only in Excel 365, it extracts the signs from a cell range and puts the corresponding count next to the signs. For example, cell range B3:B16 has 14 numbers, some positive, some negative and one a zero.

The formula counts the positive numbers and returns 6, negative numbers 7 and one zero number.

Dynamic array formula in cell D3:

The LET function makes this formula much smaller and you also need to change the cell reference in one location only in the formula.

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 61 functions in the 'Math and trigonometry' category.

Excel function categories

Excel categories

Comments (7)